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

K

kkoz84

Board Regular
Joined
Sep 8, 2015
Messages
82
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



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 335"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]AA[/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD]Bev Howarth[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Bev Howarth[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Beverley Drew[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Beverley Drew[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Carole Collins[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Cheryl Baker[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Cheryl Baker[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]Donna Robinson[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Dawn Cooper[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Emily Harper[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Donna Robinson[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Gemma Caithness[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Emily Harper[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]Linda Saunders[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Emma Horton[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Lisa McDonnell[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Gemma Caithness[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Rhiannon Kinally[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Jan Gardiner[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Sarah Bishop[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Linda Saunders[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Zoe Miller[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Lisa McDonnell[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lynne Simpkins[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rhiannon Kinally[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah Bishop[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zoe Miller[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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)),"")
 
Upvote 0
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]

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


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.
 
Upvote 0
kkoz84 said:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Bev Howarth[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Bev Howarth[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Beverley Drew[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Beverley Drew[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Carole Collins[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Cheryl Baker[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Cheryl Baker[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Donna Robinson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Dawn Cooper[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Emily Harper[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Donna Robinson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Gemma Caithness[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Emily Harper[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Linda Saunders[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Emma Horton[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Lisa McDonnell[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Gemma Caithness[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Rhiannon Kinally[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Jan Gardiner[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Sarah Bishop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Linda Saunders[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Zoe Miller[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Lisa McDonnell[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Lynne Simpkins[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Rhiannon Kinally[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl156, width: 114"]Sarah Bishop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
Click to expand...

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)),"")
 
Upvote 0
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
 
Upvote 0
kkoz84 said:
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
Click to expand...

We are missing a paren...

C2, control+shift+enter, copy across, and down:
Rich (BB code): 

=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)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
help on SUM
Replies
5
Views
6K
mvptomlinson
M

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top