Dataset refresh from worksheets

[wisemank]

Hi there,

I have many worksheets that are used to track suppliers and I want to collect all into a dataset for a pivot table to refresh and summarize quickly. Each supplier sheet is named by the supplier and I wanted to copy each column in the supplier sheet and loop through the dataset sheet to refresh and or update. Below is data from one supplier starting always at Column A and ending at Column F. I wanted the dataset to reflect the supplier in column A (suppliers Worksheet name), then paste the information relative to each supplier.

Tool#	W.O #	Early off parts	External Buyoff	Homeline Buyoff	Homeline Plant
135054	15276	12/30/2020	2/16/2021	2/23/2021	Industrial Dr.
135056	15276	12/30/2020	2/16/2021	2/23/2021	Industrial Dr.
176070	15334	7/2/2021	9/15/2021	10/8/2021	Industrial Dr.
176072	15335	7/19/2021	8/6/2021	8/25/2021	Industrial Dr.
176079	15341	N/A	10/7/2021	10/21/2021	Industrial Dr.
176081	15341	10/8/2021	9/29/2021	10/11/2021	Industrial Dr.
176082/83	15341	9/29/2021	9/8/2021	9/29/2021	Industrial Dr.
176084/85	15341	9/17/2021	9/17/2021	9/30/2021	Industrial Dr.

Results:

Shop	Tool#	W.O #	Early off parts	External Buyoff	Homeline Buyoff	Overall % Complete
Cleveland St.	105057	15345	8/19/2021	8/27/2021	9/3/2021	50%
Cleveland St.	176088	15341	N/A	8/6/2021	8/27/2021	50%
Cleveland St.	176089	15341	N/A	8/6/2021	8/27/2021	50%
Cleveland St.	176091	15341	N/A	9/6/2021	9/27/2021	25%
DieNamic	176087	15341	N/A	9/6/2021	9/27/2021	25%
DieNamic	237016	15368	N/A	11/2/2021	11/16/2021	25%
DieNamic	237024	15371	N/A	11/2/2021	11/16/2021	25%

Thanks,

Kip

 

[RasGhul]

You could use Power Query to get data & transform, then Append into one data set for pivot table.