Extracting Distinct ID

Question

I have Sheet 1 that has Column H with an Employee ID unique to an individual. There are multiple lines per employee however because there is quarterly data in the file. Data will be added each quarter to this sheet of the file.

In another table I would like to give a summary by employee ID. the information I would be pulling into the summary page would be an index and match function off of 4 other sheets (one for each quarter using the EE ID and the match)

What I am trying to accomplish is that the summary sheet would update with any employee ID that is added to Sheet 1 throughout the year.

I was trying to do the unique function but for some reason the lookup doesn't seem to be working all the time? Do you have suggestions on how I can accomplish this dynamic summary sheet?

Answer 1

Yes, you can. From multiple sheets in a workbook, from multiple workbooks or multiple other sources ...

.

https://www.youtube.com/watch?v=sLW3NbeGDy8 

.

Simplest if they have the same structure:

https://trumpexcel.com/merge-tables/

.

https://trumpexcel.com/combine-multiple-worksheets/ 

.

From multiple files:

https://www.howtoexcel.org/how-to-import-multiple-files-with-multiple-sheets-in-power-query/ 

.

PowerQuery is just that, POWERFUL! What you can do with it is limited mainly by your imagination. Breaking your end result into small, logical steps. Sometimes the intermediate steps require "twisted" logic.. Oz du Soleil is really creative when it comes to using PowerQuery: https://www.youtube.com/channel/UCZgOVykPoRbSZQfY9YysiRQ

Answer 2

So can I have a pivot table that is sourced off multiple sheets?

Answer 3

Hi,

I don't see issues to create the result with a Pivot Table.. maybe Power Pivot must be included. Show me a sample file.

Andreas.

Answer 4

A Pivot table will work as far as giving me the unique ID. The issue is I want to take that unique ID from the pivot table and dynamically "match" to four other sheets (Q1 sheet, Q2 sheet, etc. to give me the incentive amount.

The first sheet I have is which contains a unique ID but could have multiple lines due to a separate line for each quarter

Sheet 1

Unique ID, Name, Q1

Unique iD, Name, Q2

The incentive amount is on 4 separate sheets one for each quarter

Q1 Sheet, Q2, Sheet, Q3 Sheet, Q4 Sheet

This file has a bunch of columns the two that are meaningful here are

Unique ID, Incentive Amount

On a New sheet I want to create a summary table pulling Information from Sheet 1 and the quarterly sheets so 5 different sheets in total.

Result I am looking for is

unique ID	Name	Q1	Q2	Q3	Q4
123	joe smith	50	100	75	100
234	john doe	76	98	100	98

That is why I was trying to pull dynamically to the new tab the list of unique IDs on Sheet 1 and then do an index-match to lookup the values from the other sheets. Just trying to figure out why the lookups aren't always working off the unique ID. Could it be a format of the unique ID that would be causing it?

Answer 5

Use a Pivot table.

You said you have quarterly data, that means for me you have a date column (or something similar like "Q1/2022").

In a Pivot table move the date field to the Rows section and the ID field below.
Whatever you want to calculate to the Values section.

Create a PivotTable to analyze worksheet data - Office Support

That's it.

If you need further help I need to see a sample file with some dummy data.

Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose Share.
Click Copy Link in the lower left edge (no need to enter an email).
Copy the link and paste it here.

Andreas.