Multiple Reports from single static report based on column value

Mydako

New Member
Joined
Apr 19, 2018
Messages
23
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.


PODateItemNameQtyPriceCode
1232019/10/07456Stuff3$5In stock
1242019/10/07457Morestuff4$10Discontinued
1252019/10/07458Potato10$13Out of Stock
1262019/10/07457Morestuff6$10Discontinued

<tbody>
</tbody>

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

PODateItemNameQtyPriceCode

<tbody>
</tbody>
1242019/10/07457Morestuff4$10Discontinued

<tbody>
</tbody>
1262019/10/07457Morestuff6$10Discontinued

<tbody>
</tbody>

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.
 

Mydako

New Member
Joined
Apr 19, 2018
Messages
23
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,821
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
 
Last edited:

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top