Forum Discussion
Solved
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 y...
- oops, my bad. totally missed that. lol.
do you know how to create tables and use structured references? I recommend that if you can.
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
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.
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.
- Excel
New data will be copied in manually I believe but in the same format. So for 2023 it will be copy and pasted with the same column order as in the sheets i've shared.
Fully functional - a user cna just copy in the data and it'll all line up in the comparison sheet
Ok great, thanks!- You didn't answer the question of Excel vs sheets
In excel I would recommend converting the tables to be 'Format as a Table' and name those tables something like NewData and OldData and then use structured references accordingly and use Column sorting
In sheets I would change the reference to be 'open ended' like A1:G to indicate start with cells A1:G1 and include everything to the last data row in those columns. Then as you paste data of different length it will include all that data.- At the beginning of my answer I mentioned Excel, won't be doing it in sheets.
That's great thanks, I will try it out!