How to list only active rows from a range?

[alhaste]

Hi everyone,


In "Sheet1", (I've got a long list of items where some will be active (denominated by a 1) and some inactive (denominated by a 0).
I want excel to automatically list these items in "Sheet2" but only listing the active ones.

I'm looking for a formula that will populate the rows, by skipping potentially inactive items and display the next active item, all through the list on "sheet1"

I tried using an IF statement to find the next active item, but realized that there could be situations where there could be 3-4-5-10 inactive items before the next active items, so in the end, that would become a horribly long IF statement.

Or maybe I'm just not using it correctly?

Also tried using VLOOKUP but could not make it work since I did not know how to have VLOOKUP look for active/inactive.

Have googled around a bit and seem to find fractions of the solution, but not the whole solution.
Maybe I'm just not as well versed in Excel as I would like to think and the answer is really simple.
Hope you can help me.

Thank you in advance for your valuable time.


This is a snippet from sheet 1 (there are several hundred items in the list)

ID	Shelf	Number	Item	Value	Group	Active
1​	1	1	Telekort 100 kr.	100	Telekort (max 2 i alt)	1
2​	1	2	Telekort 150 kr.	135	Telekort (max 2 i alt)	1
3​	1	3	Velo Mint Mild	50	Snus	1
4​	1	4	Velo Freeze Stærk	55	Snus	1
5​	1	5	Velo Ice Cold	55	Snus	1
6​	2	1	LA Rød 100	61	Cigaretter (max 2 i alt)	1
7​	2	2	LA Blue 100	61	Cigaretter (max 2 i alt)	1
8​	2	3	Prince Rød	65	Cigaretter (max 2 i alt)	1
9​	2	4	Prince Original (light)	65	Cigaretter (max 2 i alt)	1
10​	2	5	Marlboro Red	60	Cigaretter (max 2 i alt)	1
11​	2	6	Marlboro One (light)	60	Cigaretter (max 2 i alt)	1
12​	2	7	Al Capone Cigarillos	45	Cigaretter (max 2 i alt)	1
13​	2	8	Inhalator 10 mg. 18 stk.	100	Nicorette Produkter	1
14​	2	9	Inhalator 10 mg. 42 stk.	210	Nicorette Produkter	1

This is a snippet from sheet 2

Vare			Value
Telekort 100 kr.	100,00​
Telekort 150 kr.	135,00​
Velo Mint Mild	50,00​
Velo Freeze Stærk	55,00​
Velo Ice Cold	55,00​
LA Rød 100	61,00​
LA Blue 100	61,00​
Prince Rød	65,00​
Prince Original (light)	65,00​
Marlboro Red	60,00​
Marlboro One (light)	60,00​
Al Capone Cigarillos	45,00​
Inhalator 10 mg. 18 stk.	100,00​
Inhalator 10 mg. 42 stk.	210,00​

 

[Fluff]

Hi & welcome to MrExcel.
How about

=FILTER(Sheet1!D2:E1000,Sheet1!G2:G1000=1)

 

[alhaste]

Hi Fluff,

Thank you so much for your reply.

I knew it was super simple. I never thought of actually having a filter as a formula, only as a "physical" filter.
It works like a charm. And I must look like a complete Excel noob.

I had to undo the merged cells in Sheet1, because it made a "Spill!" error, but that is fine.
That meant I had to split the formula into two formulas, one for row D and one for row E, which works perfectly.

An added question, if I may:
Is it possible to "clip" the long line of items in Sheet2 so that we put it in two columns, and have them go over two pages?
For the purpose of printing it.

I tried doing it
For instance:
=FILTER(sheet1!D2:D73;sheet1!G2:G73=1)
and
=FILTER(sheet1!D74:D146;sheet1!G74:G146=1)
and
=FILTER(sheet1!D147:D231;sheet1!G147:G217=1)
and
=FILTER(sheet1!D232:D337;sheet1!G217:G287=1)

Formula wise it works, but whenever there is an inactive item, it will just shorten the list in the respective column, leaving a blank space at the bottom, instead of filling all the rows in the column

Is there any way around this problem that can be done with an Excel formula?

 

[alhaste]

Aaah wait I think got it, I'll make another sheet with only the active items and then from there make the list for the printout, that should solve it.

Unless you have some super smart formula that can do it in one fell swoop?

 

[Fluff]

There should be no need to "split" the formula for col D & E unless you do not want them in consecutive columns.

 

[alhaste]

There should be no need to "split" the formula for col D & E unless you do not want them in consecutive columns.

Sorry if I did not make sense of why I had to split the formula.

What I originally posted was with merged cells in sheet 2, so it looked like there were consecutive columns.
When I had to unmerge them, they were several columns apart.
That is why I had to split your formula.

Again, it works wonders, and I just made a "helper sheet" with your brilliant suggestion and from there took those values to the printout sheet.
So again, please let me express my sincerest thank you and apologize for making such a long post for such a simple question.

 

[Fluff]

Glad to help & thanks for the feedback.