Home

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

CatherineC
Occasional Visitor

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

1 Reply

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.