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

[TABLE="width: 462"]
<colgroup><col style="width: 414pt; mso-width-source: userset; mso-width-alt: 20187;" width="552"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl74, width: 552, bgcolor: transparent"][/TD]
[TD="class: xl74, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]tariffs;[/TD]
[TD="class: xl74, bgcolor: transparent"]Vol[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 500 mins & 500 text 2-3 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]


[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 1000 mins & 1000 text 2-10 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 2000 mins & 2000 text 2-20 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 2500 mins & 2500 text 2-25 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 3500 mins & 3500 text 2-35 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 5000 mins & 5000 text 2-50 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 10000 mins & 10000 text 2-100 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 25000 mins & 2500 text 2-250 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 50000 mins & 50000 text 2-500 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer 100000 mins & 100000 text 2-1000 conns[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer Unlimited mins & unlimited texts[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sharer Secondary [/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Mobile Broadband 3GB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Mobile Broadband 5GB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Mobile Broadband 10GB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Solo 500MB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Solo 1GB @ £20[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Solo 1GB @ £25[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Solo 1GB @ £30[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Solo 2GB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Solo 5GB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Solo 10GB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sim Only 1GB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sim Only 2GB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]Sim Only 5GB[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[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 :-

[TABLE="width: 644"]
<tbody>[TR]
[TD]tariffs;[/TD]
[TD]Vol[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 500 mins & 500 text 2-3 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Mobile Broadband 5GB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 1000 mins & 1000 text 2-10 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Mobile Broadband 10GB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 2000 mins & 2000 text 2-20 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Solo 2GB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 2500 mins & 2500 text 2-25 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Solo 10GB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 3500 mins & 3500 text 2-35 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Sim Only 5GB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 5000 mins & 5000 text 2-50 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 10000 mins & 10000 text 2-100 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 25000 mins & 2500 text 2-250 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 50000 mins & 50000 text 2-500 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer 100000 mins & 100000 text 2-1000 conns[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer Unlimited mins & unlimited texts[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sharer Secondary [/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mobile Broadband 3GB[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mobile Broadband 5GB[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mobile Broadband 10GB[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo 500MB[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo 1GB @ £20[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo 1GB @ £25[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo 1GB @ £30[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo 2GB[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo 5GB[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Solo 10GB[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sim Only 1GB[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sim Only 2GB[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sim Only 5GB[/TD]
[TD="align: right"]3[/TD]
[TD]


[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col><col></colgroup>[/TABLE]

 

[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