data populate according ID from 2nd sheet

[AaruJaan]

Hi Experts,
I have file with 2 tabs, 1st tab have data and 2nd tab I want data according the ID number, please check the screenshot and excel file.

Thanks in advance.
Farhan

[dflak]

This could be solved with a pivot table. Since you have Excel 2007, I converted the data into an Excel Table. The pivot table is built off of the Excel Table so when you add or remove data from the excel table, the pivot table does not have to change.

Too bad you don't have Excel 2010 or later. Then you could use slicers and the report would look a lot more professional.

[AaruJaan]

Thanks dflak, can you create formula?

[dflak]

I can do this with formulas, but that will get you only the first matching Claim number. If you have several of the same claim number, then the pivot table shows them all. For example, I added a helper column that looks only at the first 6 characters of the claim number and made this my new filter (Column B1) for claim 528693, it pulls up two claims to show.