Hello,
I'm wondering if there is a way to pull data into a different sheet that are grouped together into similar component numbers. I have a list of asset component numbers with associated descriptions and I want to combine all the descriptions with the same component groupings (A2, A3, A4, A5, A6, etc.) and transfer to a different cell.
For example, anything that has an asset number beginning with A5, I want to be able to copy those descriptions into a single cell. The attached spreadsheet shows the example of what we want to accomplish. The Data sheet contains the list as is and the What we want sheet is what we need the original list to become.
Please let me know if you can help...any help would be very much appreciated
Thanks & have a nice day!
Annalisa
Hi Annalisa Loh,
I suggest you work with Power Query.
- The first step is to import your data into Power Query
- Then you separate your data to get the 'Assest' code.
- Then, you group your data by combining the different elements (by 'Assest')
- Finally, you put your results back into Excel (Result sheet)
BR,
Lionel
You're looking for a simple Vlookup. In your original highlighted columns I used Xlookup if you have Microsoft 365; otherwise, in the non-highlighted columns I used Vlookup.
You could do it with power query if you have it, as in the attached version of your workbook.
If you have 365 (or 2021 I think) you could use FILTER and TEXTJOIN.
Edit: apologies, I did not see that Lionel had already given you a PQ solution.
Thank you for all the replies/suggestions.
Lionel & Velouria,
Thank you for your help so that I am able to get the needed results. I have a further question:
If I do this in Power Query, is there a way to have this repeated for many workbooks without having to create a new query for each file? Basically, is there a way to have this query saved and then applied to other workbooks? Or will I need to do this for every separate report? The reports cannot be combined because they are for different sites and locations.
Any further help or suggestions would be greatly appreciated.
Thanks,
Annalisa
If the data layout is the same, you could save the workbook as a template and just copy different data sets into it.
