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

  • 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