Syncing two lists of data for one list of individuals. One list has multiple entries.

[adam36]

I need to consolidate these two lists of data into one list.

First set is just a basic list of individuals with their data.

Second set is multiple entries for those same individuals. Each entry shows a subscription to a programme. The final single sheet should have one row per individual which shows all of the programmes they are subscribed to.

Please see attached, example sheet.

The real list is 3000 IDs, so need some kind of formula to do this (or other suggestion?).


Any help massively appreciated.

[azumi]

Please check the file, not elegant with helper column

Azumi

[benishiryo]

welcome to the forum, adam. your file is a very good illustration. you could add on a little to explain the What it should look like sheet. like C2 is 1 because you could see ID1 with a subscription to Club A in Data Set 2. a clear illustration helps solve problems here extremely quickly.

i provided a solution in Data Set 1. the headers require a more complicated formula if you don't have a fixed set of Subscriptions. if you do, please type them in instead of using my formula. this is an array formula & it can slow down your workbook if it's used extensively. in C1:
=IFERROR(INDEX('Data Set 2'!$B$2:$B$3000,MATCH(0,COUNTIF($B1:B1,'Data Set 2'!$B$2:$B$3000),0))&"","")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

it helps you pick out the unique subscription name. then in C2, simply:
=COUNTIFS('Data Set 2'!$A:$A,$A2,'Data Set 2'!$B:$B,C$1)

copy down & across. if you are interested in how the unique values are obtained, it's explained in the file of my link below*Tips & Tutorials I Compiled, sheet 2

Example Sheet.xlsx

[Vuqar]

Use Pivot Table.Look at the attachment

[adam36]

Thank you for the replies! Much appreciated. I'll have a go at the various suggestions.