Condensing a list in excel

[nugentl]

Hello, I'm trying to condense a list in excel to produce a new list that omits any rows where the volume is null or 0 (using formula not vba). Is this possible? This list looks like this here tariffs is column A and Vol column B, so new list would just have 6 rows. Any advice would be greatly appreciated,
thanks
Lianne

tariffs;	Vol
Sharer 500 mins & 500 text 2-3 conns
Sharer 1000 mins & 1000 text 2-10 conns
Sharer 2000 mins & 2000 text 2-20 conns
Sharer 2500 mins & 2500 text 2-25 conns
Sharer 3500 mins & 3500 text 2-35 conns
Sharer 5000 mins & 5000 text 2-50 conns
Sharer 10000 mins & 10000 text 2-100 conns
Sharer 25000 mins & 2500 text 2-250 conns
Sharer 50000 mins & 50000 text 2-500 conns
Sharer 100000 mins & 100000 text 2-1000 conns
Sharer Unlimited mins & unlimited texts
Sharer Secondary
Mobile Broadband 3GB	2
Mobile Broadband 5GB	1
Mobile Broadband 10GB	1
Solo 500MB	0
Solo 1GB @ £20	0
Solo 1GB @ £25	0
Solo 1GB @ £30	0
Solo 2GB	1
Solo 5GB	0
Solo 10GB	1
Sim Only 1GB	0
Sim Only 2GB	0
Sim Only 5GB	3

<colgroup><col style="width: 414pt; mso-width-source: userset; mso-width-alt: 20187;" width="552"> <col style="width: 48pt;" width="64"> <tbody>
</tbody>

 

[Redwolfx]

Column D

D2 =IFERROR(INDEX($A$2:$A$1000,SMALL(IF($B$2:$B$1000>0,ROW($B$2:$B$1000)),ROWS($A$1:A2))-1,1),"") Confirm With Ctrl + Shift + Enter
E2 =IFERROR(VLOOKUP(E3,A3:B27,2,FALSE),"")

Both Formulas can be dragged down and will go blank when the information is no longer valid

 

[nugentl]

Column D

D2 =IFERROR(INDEX($A$2:$A$1000,SMALL(IF($B$2:$B$1000>0,ROW($B$2:$B$1000)),ROWS($A$1:A2))-1,1),"") Confirm With Ctrl + Shift + Enter
E2 =IFERROR(VLOOKUP(E3,A3:B27,2,FALSE),"")

Both Formulas can be dragged down and will go blank when the information is no longer valid

hmm, I've entered the formula in columns D & E and dragged down and just getting null in all cells?

 

[Redwolfx]

Does your Data start in A2 and B2?

And did you confirm The D Formula with Ctrl + Shift + Enter instead of just enter?

 

[Smitty]

Welcome to the Board!

What about AutoFilter?

 

[nugentl]

thanks! Getting there... I'd just hit enter so I've re-done column D with Ctrl + Shift + Enter and now I'm getting the following, it's missing off what should be the first entry in the list 'Mobile broadband 3GB :-

tariffs;	Vol
Sharer 500 mins & 500 text 2-3 conns	0		Mobile Broadband 5GB
Sharer 1000 mins & 1000 text 2-10 conns	0		Mobile Broadband 10GB
Sharer 2000 mins & 2000 text 2-20 conns	0		Solo 2GB
Sharer 2500 mins & 2500 text 2-25 conns	0		Solo 10GB
Sharer 3500 mins & 3500 text 2-35 conns	0		Sim Only 5GB
Sharer 5000 mins & 5000 text 2-50 conns	0
Sharer 10000 mins & 10000 text 2-100 conns	0
Sharer 25000 mins & 2500 text 2-250 conns	0
Sharer 50000 mins & 50000 text 2-500 conns	0
Sharer 100000 mins & 100000 text 2-1000 conns	0
Sharer Unlimited mins & unlimited texts	0
Sharer Secondary	0
Mobile Broadband 3GB	2
Mobile Broadband 5GB	1
Mobile Broadband 10GB	1
Solo 500MB	0
Solo 1GB @ £20	0
Solo 1GB @ £25	0
Solo 1GB @ £30	0
Solo 2GB	1
Solo 5GB	0
Solo 10GB	1
Sim Only 1GB	0
Sim Only 2GB	0
Sim Only 5GB	3

<tbody>
</tbody><colgroup><col><col span="2"><col><col></colgroup>

 

[L. Howard]

You could try this in C2 and pull down.

=IF(B2>0,A2,"")

Regards,
Howard

 

[Redwolfx]

Sorry, I copied the second Formula as your first. Try

=IFERROR(INDEX($A$2:$A$1000,SMALL(IF($B$2:$B$1000>0,ROW($B$2:$B$1000)),ROWS($A$1:A1))-1,1),"") Confirm with Ctrl + shift + Enter

 

[nugentl]

Thank you so much, all working perfectly now..... You won't believe how much time I wasted pondering over this one, I would never have worked it out! Next time I will ask for help sooner.

Out of interest what does the crtl shift enter do rather than just enter, first time I've used it

 

[Smitty]

Out of interest what does the crtl shift enter do rather than just enter, first time I've used it

That confirms the formula as an Array formula.

See: Introducing array formulas in Excel - Excel