Forum Discussion
Multi-worksheet Comparison ingestion
Hi,
I am setting up a workbook that needs to simply ingest new data that is to be compared to old data. So, in the 2019 sheet I have variables and the same variables exist in the 2023 sheet. As you can see these are in different orders. Sample data attached: https://docs.google.com/spreadsheets/d/1cW-svTPCWAjrH798oVzTJCuLjBwT8m2d/edit?usp=sharing&ouid=111952071670621667207&rtpof=true&sd=true
I need the "ID" field to be the trigger that lines up all the other variables in the row in the same order in the third sheet named "comparison". It also needs to populate in a slightly different order in terms of row. Instead of simply V1(2019), V2 (2019), V3 (2019)...it needs to be V1(2019), V1 (2023), V2 (2019), V2 (2023), V3 (2019), V3 (2023) and so on.
Is this possible?
Many thanks.
- oops, my bad. totally missed that. lol.
do you know how to create tables and use structured references? I recommend that if you can.
14 Replies
simple answer is yes. In your example GOOGLE sheet I gave a very simplistic answer
=transpose(sort(transpose(hstack(sort('2019'!A1:G5),sort('2023'!A1:G5)))))note I highlighted google sheet because i didn't see a way to sort by columns in sheets while that is possible in excel
but that equation is very dependent on
a) all IDs are the same and line up
b) all the columns are the same and line up
basically all it does is use SORT to put them in order. (note I left BOTH ID columns in to verify they are lined up)
If this is not the case then other questions come up...
What do we do when they exist in 2019 and not 2023 or vice versa? for IDs? and for column headers?- Hi mtarler,
Thank you for your reply!
That's brilliant. That does essentially solve my issue in that I can clear it up after copying all the data in. Is there any way I could this interactively? I.e new data comes in and the transpose occurs?
All the IDs will be the same, but the order will be different in 2019 and 2023. It seems your solution deal with this...
Column headers will always be consistent. I suppose some may not exist in 2023...if this is the case I think they will be omitted. So hopefully that will be ok, I can remove those cases.
My main concern is this is something I have to set up for someone else to use so it needs to be fully functional- first off will this be in google sheets or Excel?
next, how will 'new data come in'?
and of course what does it me 'to be fully functional'?
I guess what specific aspects of the solution need tweaking? You said IDs will align, columns will align (and if not they we be eliminated so in the end they will align). If instead of 2019 and 2023 you could name the sheets Old Data and New Data and they would just paste accordingly.
