billandrew
Well-known Member
- Joined
- Mar 9, 2014
- Messages
- 743
Good Morning
I am trying to show a blank in the resulting formula instead of a zero. Let me try and explain. I have a the following table (Table 1) in columns A & B.
<tbody>
</tbody>
Table 2 -
1st - I am extracting the names vertically using the following formula in cell E2. I am extending the range to allow additional names to be entered.
E2
=IFERROR(INDEX($A$2:$A$20,SMALL(IF(FREQUENCY(IF($A$2:$A$20<>"",MATCH($A$2:$A$20,$A$2:$A$20,0)),ROW($A$2:$A$20)-ROW($A$2)+1),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($E$2:E2))),"")
2nd - In cell F2 I am extracting the sales for each name using the the following formula =IFERROR(INDEX($B$2:$B$20,SMALL(IF($A$2:$A$20=$E2,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMNS($F2:F2)),ROW($A$2:$A$20)-ROW($A$2)),"")
My issue is the blank cells where no name has yet been established the result is a zero. I'd like to return a blank.
Hope my description is clear enough to interpret.
Thanks
<tbody>
</tbody>
I am trying to show a blank in the resulting formula instead of a zero. Let me try and explain. I have a the following table (Table 1) in columns A & B.
Name | Sales |
mike | 12389 |
mike | 10254 |
tom | 11658 |
tom | 20459 |
robert | 14523 |
robert | 12365 |
robert | 10248 |
george | 12489 |
glen | 45688 |
larry | 9999 |
glen | 12354 |
<tbody>
</tbody>
Table 2 -
1st - I am extracting the names vertically using the following formula in cell E2. I am extending the range to allow additional names to be entered.
E2
=IFERROR(INDEX($A$2:$A$20,SMALL(IF(FREQUENCY(IF($A$2:$A$20<>"",MATCH($A$2:$A$20,$A$2:$A$20,0)),ROW($A$2:$A$20)-ROW($A$2)+1),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($E$2:E2))),"")
2nd - In cell F2 I am extracting the sales for each name using the the following formula =IFERROR(INDEX($B$2:$B$20,SMALL(IF($A$2:$A$20=$E2,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMNS($F2:F2)),ROW($A$2:$A$20)-ROW($A$2)),"")
My issue is the blank cells where no name has yet been established the result is a zero. I'd like to return a blank.
Hope my description is clear enough to interpret.
Thanks
Names | Sales1 | Sales2 | Sales3 | Sales4 | Sales5 |
<tbody>
</tbody>