Recreating a new table removing all the 0's

**mikehu** · 07-25-2015, 09:17 AM

Hi, My first post here so go easy on me

Basically I have a basic table like the one below. Just say table starts at A1.

Name	Amount
Mike	2
John	10
Alex	0
Lucia	16
Pam	0

So using formula I would like to create a new table removing any people with a 0. I have a good look around but got nothing working so far.

Any help would be much appreciated.

Thanks

Mike

**Tony Valko** · 07-25-2015, 09:24 AM

Try this...

Data Range

	A	B	C	D	E
1	Name	Amount	------	Name	Amount
2	Mike	2		Mike	2
3	John	10		John	10
4	Alex	0		Lucia	16
5	Lucia	16
6	Pam	0
7

This array formula** entered in D2:

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$6<>0,ROW(B$2:B$6)),ROWS(D$2:D2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to E2 then down until you get blanks.

**mikehu** · 07-25-2015, 09:29 AM

Ok great, I forgot to metion im doing it in google sheets, does crtl shift and enter work on that?

**Tony Valko** · 07-25-2015, 09:32 AM

I have no experience with Google spreadsheets.

**mikehu** · 07-25-2015, 09:34 AM

ok ill have a play, formulas work just the same in google sheets.

What is the A:A bit after index, what is that referencing?

Thanks for your quick replies by the way

**mikehu** · 07-25-2015, 09:38 AM

Also it works perfectly.

Thanks you spent hours trying to do that

**oeldere** · 07-25-2015, 09:51 AM

and here is a code which does it on the orginal data.

Please Login or Register  to view this content.

**Tony Valko** · 07-25-2015, 11:58 AM

Originally Posted by mikehu

What is the A:A bit after index, what is that referencing?

A:A is a reference to the entire column A.

**Tony Valko** · 07-25-2015, 11:58 AM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

**mikehu** · 07-25-2015, 12:12 PM

Ah got you.

In google sheets it didn't need the ctrl shift and enter it just worked.

Once again thanks for your help. have marked as solved

Recreating a new table removing all the 0's

LinkBack

Thread Tools

Rate This Thread

Display

Recreating a new table removing all the 0's

Re: Recreating a new table removing all the 0's

Re: Recreating a new table removing all the 0's

Re: Recreating a new table removing all the 0's

Re: Recreating a new table removing all the 0's

Re: Recreating a new table removing all the 0's

Re: Recreating a new table removing all the 0's

Re: Recreating a new table removing all the 0's

Re: Recreating a new table removing all the 0's

Re: Recreating a new table removing all the 0's

Thread Information

Users Browsing this Thread

Similar Threads

Recreating a macro... Sum up columns until blank row

Recreating a Graph with Two Column Description

Help recreating a table (advanced formula)

Recreating Technical Indicator in Excel

Recreating a table

Contemplating recreating workbook

[SOLVED] How do I change my DSN without recreating the odbc connection?

Bookmarks

Bookmarks

Posting Permissions