Forum Discussion
Multi-worksheet Comparison ingestion
- oops, my bad. totally missed that. lol.
do you know how to create tables and use structured references? I recommend that if you can.
That's great thanks, I will try it out!
do you know how to create tables and use structured references? I recommend that if you can.
- Thank you once again for your time!
OK I didn't go back into the data but just looking at that formula I see a few things and what you said in the comment also.
As for the formula I see [#ALL] which means include both the data AND the headers so ROW1 will be the headers and ROW2 is the first line of data. I think you want ... INDEX( OriginalData, MATCH(...
Then COLUMN(OriginalData[[#Headers],[V1(2019)]]) has a couple things.
a) if you just need COLUMN() of it then just OriginalData[V1(2019)] should be enough since that returns that column of data. by adding [#Headers],[V1(2019)] you are calling out that specific header cell, which is fine but not necessary for this purpose.
b) COLUMN() returns the WORKSHEET column NOT the column number in the table. most likely the table starts in Column A and therefor it doesn't matter, but if not you need to offset that amount by the beginning column of the table. For Example in a general case: COLUMN(Table1[[Column1]])-@COLUMN(Table1)+1
c) So IF you use the little drag box to fill to the right then the table column will increment accordingly for the V1 but will also increment for the column [id] and you don't want that so either use [ID}:[ID] to trick it into staying locked or use something like
MATCH(C$1, OriginalData[#Headers],0)
Where C$1 is the header of the new table and in this case would have V1(2019)
Then instead of the drag fill option use the copy paste option to copy to the cells to the right and the table references won't change but the cell reference C$1 will move.
I hope that helpsBTW, if you are using Excel 365 then I would recommend using FILTER, XMATCH, and other newer functions.
- =IFERROR(INDEX(OriginalData[#All], MATCH([@ID], OriginalData[ID], 0), COLUMN(OriginalData[[#Headers],[V1(2019)]])), "")
Hi,
In the context of the data I shared I have tried working using this formula but there are still errors. Can you advise on the issue at all?
My goal (as a reminder) is to transport all the data across using structured references so as to allow new data to be added. Respecting the matching IDs whilst indexing data based on column headings too.
Thanks in advance - that's great. let us know if questions do come up.
- It's going okay actually, many thanks!
mtarler Hi,
Could you give any more insight into using tables and structured references? I've constructed the tables but I am not sure where to go from here...any resources would be welcome.
Thanks again for your help!No worries! I can't say I'm familiar but I will look into it as a potential solution 🙂 Thanks for all of your help!