Results 1 to 2 of 2

Thread: Compare MONTH/YEAR of 2 lists

  1. #1

    Post Compare MONTH/YEAR of 2 lists



    Register for a FREE account, and/
    or Log in to avoid these ads!

    We have 2 huge different excel [2013] payment lists, around 5000 members of our club members, we need to compare this list with a list, around 1000 rows, of their unpaid month fee.

    As you can see on following table, we would like to transpose results, because they can be multiple date presents into the unpaid month fee list.

    ID Name Paid Unpaid Date 1 Unpaid Date 2 Unpaid Date 3 Cancellation
    116 JOSH BLACK 20/08/2013
    118 KELVIN JONES 11/07/2013 21/01/2013 2/05/2013 13/07/2015
    137 ADAM PETTY 16/11/2016
    132 KAREN MONEY 10/03/2015
    500 LARENS MOUNTAINS 20/08/2013
    18 INGRID LITTLE 3/02/2017
    141 NAOMI NICE 7/10/2015
    176 KELVIN LOVE 9/10/2015

    I have the idea to lookup into unpaid fee list if ID is present copy transposed date to payment list but no idea how to do it.

    Thx in advanced

  2. #2

    Post Solved

    Hi,

    Someone give me the answer in other Excel Forum, I want to share with you guys.

    Code:
    =IFERROR(INDEX(Unpaid!$C:$C,SMALL(IF(Payments!$A2=Unpaid!$A:$A,ROW(Unpaid!$A:$A),""),COLUMN()-3)),"")
    Press CTRL + SHIFT + ENTER

    PS: [-3] is the number of columns of "Payments" sheet.

    Attached workbook.

    Hoop this helps others

    FuLaTiNs
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •