Multiple Reports from single static report based on column value

[Mydako]

Hello,
Either it’s Monday morning, or I’m overestimating my abilities, or both.

I’m trying to figure out a way to generate multiple reports automatically based off a static report and the values in it.

We get a report with hundreds of thousands of items on it, and they’re all grouped together. What I want is the ability to take this report, and then automatically on other tabs make reports categorized by a cell value, in this case the code that tells us if an item is discontinued, out of stock, in stock, etc.

In my mind my initial thought was the inelegant =if(CodeCell=Discontinued, First Cell in that Row,””) and repeat that for the entire row. It’s not elegant, and it leaves blank spots in the new report when the item lacks that code.

It doesn’t strike me as too hard, and I’ve done a lot more complicated, but for some reason my brain is getting stuck on this.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PO[/TD]
[TD]Date[/TD]
[TD]Item[/TD]
[TD]Name[/TD]
[TD]Qty[/TD]
[TD]Price[/TD]
[TD]Code
[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]2019/10/07[/TD]
[TD]456[/TD]
[TD]Stuff[/TD]
[TD]3[/TD]
[TD]$5[/TD]
[TD]In stock
[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]2019/10/07[/TD]
[TD]457[/TD]
[TD]Morestuff[/TD]
[TD]4[/TD]
[TD]$10[/TD]
[TD]Discontinued
[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]2019/10/07[/TD]
[TD]458[/TD]
[TD]Potato[/TD]
[TD]10[/TD]
[TD]$13[/TD]
[TD]Out of Stock[/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]2019/10/07[/TD]
[TD]457[/TD]
[TD]Morestuff[/TD]
[TD]6[/TD]
[TD]$10[/TD]
[TD]Discontinued[/TD]
[/TR]
</tbody>[/TABLE]

For instance with the above I'd like to generate a Discontinued report that would return the below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PO[/TD]
[TD]Date[/TD]
[TD]Item[/TD]
[TD]Name[/TD]
[TD]Qty[/TD]
[TD]Price[/TD]
[TD]Code[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]124[/TD]
[TD]2019/10/07[/TD]
[TD]457[/TD]
[TD]Morestuff[/TD]
[TD]4[/TD]
[TD]$10[/TD]
[TD]Discontinued[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]126[/TD]
[TD]2019/10/07[/TD]
[TD]457[/TD]
[TD]Morestuff[/TD]
[TD]6[/TD]
[TD]$10[/TD]
[TD]Discontinued[/TD]
[/TR]
</tbody>[/TABLE]

What I currently have is
=if(G2="Discontinued,A2,"") | =if(G2="Discontinued,B2,"") | =if(G2="Discontinued,C2,"") and so on in a row.

I feel like it's too early, cause I'm sure I've done similar and this shouldn't be a problem, but I can't get this to work.

While I know filtering and copy and pasting would work these reports are usually 500-1000 rows, and are done daily. So if I could automate instead of filter/copy/paste it could save a lot of time.

 

[sandy666]

did you try Power Query to do that?

 

[Mydako]

Ah sorry, forgot to mention.

Our systems are on Citrix (ugh) which means add-ons, and VBA don't work, among other things. This needs to be in formulas for basic Excel out of the box, so to speak.

 

[sandy666]

what is your Excel version?

 

[Mydako]

It's Excel 2016.

 

[sandy666]

so there is build-in Power Query aka Get&Transform
if it doesn't work you can use Pivot Tables, each PT for each Code
or one Pivot Table with Code filter