Array Formula To Extract Info From 3 Worksheets & Plot Into 1 Table

[unpluggedmusic]

Dear Experts,

My work requires me to extract individual client's information and list them into a table. But the problem is, we have 3 lists and each of the list is 1000 long, therefore a formula is required to automatically extract information when a client's name is selected from a dropdown list.

I need to first make the dropdown list (Data Validation) possible, with all clients' names listed, removed duplicates, removed blanks.

Then i need to populate the table automatically with an array formula to extract all product ID that belongs to this specific client.

I have tried my best but my excel knowledge is not good enough to accomplish this. I have made a sample excel file with fictatious names so that everyone can assist through my sample sheet.

Thanks a million again!!!

regards,
LIM

[Pete_UK]

In the attached file I've shown how you can get the list of unique names for your data validation drop-down.

First, you should obtain a composite list from your three sheets, allowing room for expansion. I've done this in column R.

Then you can use this array* formula to extract uniques from that composite list, and to bunch them all together:

Please Login or Register  to view this content.

I've put this in T2, then copied down. You will need to change the row reference in red to suit your lists (i.e. about 3000), but that might take some time to calculate.

*NOTE that an array formula should be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>

Then you can use this unique list dynamically as the source for your data validation. I've set up a dynamic named range with this formula:

Please Login or Register  to view this content.

in Name Manager, so that the data validation in D4 shows only the list of names.

Hope this helps (for now).

Pete

[unpluggedmusic]

Hi Pete_UK,

Thanks for your help! Truely appreciate. You solved half of my problem. Thanks a lot!

[Pete_UK]

Okay, well here's the other half of the problem solved (attached). I've added a helper column to each of the three sheets which basically identifies the records which match the selection on the Main sheet and creates a sequence for each matching record. The sequence continues onto the next sheet in turn. I've also added a small table in the main sheet which finds the largest number allocated within each sheet, so that the sheet can be identified (in column A). This formula in C8, which is copied down the column,:

Please Login or Register  to view this content.

retrieves the appropriate data. Just change the name using the drop-down in D4 to get another set of records.

Hope this helps.

Pete