Extracting data from columns without ZERO's. Using automatic formula

[kkoz84]

Hi guys,

Please see below, I really hope you could help me. I would like to extract data from column A and B as below, so that it looks like on the right in column AA BB.

In general I want to extract data without 0 next t the consultant name. And I need to do it with formula, so that it will automatically extract data in the way i want when data on the left will change as I want to use it in my dynamic chart.

Any help much appreciated.

Hope everything is clear if not please let me know i will give more details.

Regards Luke

A B AA BB Bev Howarth 1 Bev Howarth 1 Beverley Drew 3 Beverley Drew 3 Carole Collins 0 Cheryl Baker 2 Cheryl Baker 2 Donna Robinson 1 Dawn Cooper 0 Emily Harper 2 Donna Robinson 1 Gemma Caithness 1 Emily Harper 2 Linda Saunders 1 Emma Horton 0 Lisa McDonnell 1 Gemma Caithness 1 Rhiannon Kinally 2 Jan Gardiner 0 Sarah Bishop 2 Linda Saunders 1 Zoe Miller 1 Lisa McDonnell 1 Lynne Simpkins 0 Rhiannon Kinally 2 Sarah Bishop 2 Zoe Miller 1 <tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

AA1, control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(1/$B$2:$B$20),1))

AA2, control+shift+enter, not just enter, copy across, and down:

=IF(ROWS(AA$2:AA2)<=$AA$1,INDEX(A$2:A$20,SMALL(IF(ISNUMBER(1/$B$2:$B$20),ROW(A$2:AB$20)-ROW(A$2)+1),ROWS(AA$2:AA2)),"")

 

[kkoz84]

Thank you so much, I will try it now and let you know how it went!

 

[kkoz84]

	A	B	C	D
1	Bev Howarth <tbody> </tbody>	1	Bev Howarth <tbody> </tbody>	1
2	Beverley Drew <tbody> </tbody>	3	Beverley Drew <tbody> </tbody>	3
3	Carole Collins <tbody> </tbody>	0	Cheryl Baker <tbody> </tbody>	2
4	Cheryl Baker <tbody> </tbody>	2	Donna Robinson <tbody> </tbody>	1
5	Dawn Cooper <tbody> </tbody>	0	Emily Harper <tbody> </tbody>	2
6	Donna Robinson <tbody> </tbody>	1	Gemma Caithness <tbody> </tbody>	1
7	Emily Harper <tbody> </tbody>	2	Linda Saunders <tbody> </tbody>	1
8	Emma Horton <tbody> </tbody>	0	Lisa McDonnell <tbody> </tbody>	1
9	Gemma Caithness <tbody> </tbody>	1	Rhiannon Kinally <tbody> </tbody>	2
10	Jan Gardiner <tbody> </tbody>	0	Sarah Bishop <tbody> </tbody>	2
11	Linda Saunders <tbody> </tbody>	1	Zoe Miller <tbody> </tbody>	1
12	Lisa McDonnell <tbody> </tbody>	1
13	Lynne Simpkins <tbody> </tbody>	0
14	Rhiannon Kinally <tbody> </tbody>	2
15	Sarah Bishop <tbody> </tbody>	2

<tbody>
</tbody>

I have tried to enter formulas you gave me into my cells. I had to change references as columns I had on my spreadsheets had different names. But i check all four times and second formula still is not working.Not sure where is the problem.

Could we start with this layout as now I have everything set up as here as so i will just be able to copy formulas and see if they will work am not sure but maybe i do somewhere mistake?

Apologies if i wasted your time and bunch of thanks for help.

 

[kkoz84]

And just to mention that I have did ctrl shift enter

 

[Aladin Akyurek]

	A	B	C	D
1	Bev Howarth <tbody> </tbody>	1	Bev Howarth <tbody> </tbody>	1
2	Beverley Drew <tbody> </tbody>	3	Beverley Drew <tbody> </tbody>	3
3	Carole Collins <tbody> </tbody>	0	Cheryl Baker <tbody> </tbody>	2
4	Cheryl Baker <tbody> </tbody>	2	Donna Robinson <tbody> </tbody>	1
5	Dawn Cooper <tbody> </tbody>	0	Emily Harper <tbody> </tbody>	2
6	Donna Robinson <tbody> </tbody>	1	Gemma Caithness <tbody> </tbody>	1
7	Emily Harper <tbody> </tbody>	2	Linda Saunders <tbody> </tbody>	1
8	Emma Horton <tbody> </tbody>	0	Lisa McDonnell <tbody> </tbody>	1
9	Gemma Caithness <tbody> </tbody>	1	Rhiannon Kinally <tbody> </tbody>	2
10	Jan Gardiner <tbody> </tbody>	0	Sarah Bishop <tbody> </tbody>	2
11	Linda Saunders <tbody> </tbody>	1	Zoe Miller <tbody> </tbody>	1
12	Lisa McDonnell <tbody> </tbody>	1
13	Lynne Simpkins <tbody> </tbody>	0
14	Rhiannon Kinally <tbody> </tbody>	2
15	Sarah Bishop <tbody> </tbody>	2

<tbody>
</tbody>

I have tried to enter formulas you gave me into my cells. I had to change references as columns I had on my spreadsheets had different names. But i check all four times and second formula still is not working.Not sure where is the problem.

Could we start with this layout as now I have everything set up as here as so i will just be able to copy formulas and see if they will work am not sure but maybe i do somewhere mistake?

Apologies if i wasted your time and bunch of thanks for help.

C1, control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(1/$B$1:$B$20),1))

C2, control+shift+enter, not just enter, copy across, and down:

=IF(ROWS(C$2:C2)<=$C$1,INDEX(A$1:A$20,SMALL(IF(ISNUMBER(1/$B$1:$B$20),ROW(A$1:A$20)-ROW(A$1)+1),ROWS(C$2:C2)),"")

 

[kkoz84]

Hi again,

I copied formula in C1 and it works and gives me value 10.

But in C2 when i copy it excel tells me to add missing ")" and it adds it at the end of formula. Then i just get #value error and when i copy across from C12:D15 i get falses, any ideas why would this happen? I do use ctrl shift enter and all is exactly same as table here

Regards Luke

 

[Aladin Akyurek]

Hi again,

I copied formula in C1 and it works and gives me value 10.

But in C2 when i copy it excel tells me to add missing ")" and it adds it at the end of formula. Then i just get #value error and when i copy across from C12:D15 i get falses, any ideas why would this happen? I do use ctrl shift enter and all is exactly same as table here

Regards Luke

We are missing a paren...

C2, control+shift+enter, copy across, and down:

=IF(ROWS(C$2:C2)<=$C$1,INDEX(A$1:A$20,SMALL(IF(ISNUMBER(1/$B$1:$B$20),
   ROW(A$1:A$20)-ROW(A$1)+1),ROWS(C$2:C2)),"")

 

[kkoz84]

thanks a lot works perfect

 

[Aladin Akyurek]

thanks a lot works perfect

You are welcome. Thanks for the update.