Forum Discussion

CatherineC's avatar
CatherineC
Copper Contributor
Dec 13, 2018

Matching data in columns in one worksheet and placing in another - formula? Pivot table? So confused

Hi!  

 

I have two large lists of the same data, one is our master contacts list, and the other is  the Xmas Card list. 

 

How do I quickly create a formula which consolidates what appears in the Xmas Card list 2018 column (1 for each contact sent a card) so that it appears in the same column in the master list? 

 

Please help as I'm rubbish cross referencing and my eyes are crossing all over the place! 

 

Thanks, 


Catherine

Excel
Formulas and Functions
  • Arul Tresoldi's avatar
    Arul Tresoldi
    Iron Contributor
    Dec 13, 2018

    Hi there; a quick question: the master contact list gathers all the contact; do the xmas list do the same BUT adding a check in the near column, if the card was sent?

     

    Master:

    Alpha

    Bravo

    Charlie

     

    XMas:

    Alpha

    Bravo

    Charlie 1

     

    Or it's just like this:

     

    Master:

    Alpha

    Bravo

    Charlie

     

    XMas:

    Charlie

     

    In the first case, you can use the third column in XMas sheet with "A2&B2" formula (in row 2); then you add a column in Master with the formula "COUNTIF(XMas!A2:A2000;A2&1)" and if it says 1 means that you sent the card, if it's 0 means not.

     

    In the second case, you can just use the same but without the "1" and no need to add third column in XMas: "COUNTIF(XMas!A2:A2000;A2)"; same, if it's 1 means you sent it, if it's more than 1 means you have the same contact more than 1 time, if it's 0 means you did not sent cards.

Resources