Consolidating Data from different worksheets

[iriex]

Can some please assist me with the following: I've tried a bunch of code which has somewhat helped but can't seem to get what I'm looking for.

Note: Each Row specified is Actually its own sheet with the same header. Sheet1, Sheet2, Sheet3, Sheet4, & Sheet5

Expected Outcome:

• Name - Consolidated to just Canadian Tire Associate Stores (listed in ascending order-- this field will actually be numbers if that matters)
• Field Name - Consolidated to just TEST
• Description - inserted column that should be blank throughout
• Unique Values are all consolidated into one cell (sort in ascending order)
• Sheets implys the different worksheets the data was pulled from.
• NOTE: The columns with the numbers are ignored (which are just counts) and are not needed on the consolidated sheet.

In addition, I would like if possible, once i filter by sheet (using Sheet1 for example), it would only show me the associate values with that worksheet. In this case, values would be Automotive Repair, Garden Centre and HardwareStore, and the rest of the columns remaining the same.

THanks in advance

 

[dermie_72]

Re: PLEASE PLEASE PLEASE HELP ME Consolidating Data from different worksheets

it may be easier to just copy all the data onto one sheet and then do a pivot table. Using name/field/value in the row lables, and sum of < > in the values field.

 

[Teeroy]

Re: PLEASE PLEASE PLEASE HELP ME Consolidating Data from different worksheets

What do you do where the value (e.g. Automotive Repair) when it comes from several sheets? Do you only the first occurrence shown, or do you want all sheet names in one cell, separated by a comma?

 

[iriex]

Re: PLEASE PLEASE PLEASE HELP ME Consolidating Data from different worksheets

Would want all sheet names in one cell as illustrated. Note the 1st picture, i illustrate each row as if its coming from different sheets. So in that case, a mini merge has happen already, but not completely what I want.

Also, once the data is merge, If i filter by Sheet(using Sheet1 for example), it would only show me the values for that worksheet. In this case, values would be Automotive Repair, Garden Centre and HardwareStore, and the rest of the columns remaining the same. All listed under each other and not delimited by a comma within the same cell.

So basically, if i filter by Sheet1, all the data would appear row by row depending on how much data is on the sheet.