Forum Discussion
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
- Arul TresoldiIron Contributor
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.