You don’t need powerautomate. We will set this up once, and you never have to set it up again, you just copy the new 30k row report, drop it in the first tab, and the subset tab will lookup data in the first tab.
I’m going to simplify this, you just need to do this for however many columns there are. Let’s say I have product code in column A and sales numbers in column B.
Create your new tab
identify your primary key (product code)
manually enter (or copy over) all 4 product codes
In this case, that’s in column A. (A2-A5).
if you have any decent excel, you should have an XLOOKUP function. In B2, you would have the following formula:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
so it would look like this:
=XLOOKUP($A2, Maintab!’$A:$A, Maintab!’B:B,,0,)
this will find the product code in A2 and bring over the proper value from column B on the main tab to populate column B on the subset tab.
Notice the $. Column A doesn’t change (you always want to reference the product code, but the row does and the column you are pulling data from does.
you can copy this down, and if there were more columns, you could copy it right.
for xlookup, the primary key doesn’t have to be on the left. If you don’t have xlookup, you have to use vlookup. It’s similar, but you can only pull data to the right of product code. If you need help with that, let me know.
good luck!
