Extract a BLANK

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.

NameSales
mike12389
mike10254
tom11658
tom20459
robert14523
robert12365
robert10248
george12489
glen45688
larry9999
glen12354

<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

NamesSales1Sales2Sales3Sales4Sales5

<tbody>
</tbody>
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You can hide zero. If you are using version 2010, in File/Options/Advanced/Display options for this worksheet uncheck Show a zero in cells tat have zero value.
 
Upvote 0
You don't show what columns your data is in, but maybe this...
=if(A1="","",your-formula)
 
Upvote 0
billandrew,

Here is a macro solution for you to consider that will adjust to the number of raw data rows, plus.

Sample raw data:


Excel 2007
ABCDEFGHIJKL
1NameSales
2mike12389
3mike10254
4tom11658
5tom20459
6robert14523
7robert12365
8robert10248
9george12489
10glen45688
11larry9999
12glen12354
13
14
15
16
17
18
19
20
Sheet1


And, after the macro:


Excel 2007
ABCDEFGHIJKL
1NameSalesNamesSales1Sales2Sales3
2mike12389mike1238910254
3mike10254tom1165820459
4tom11658robert145231236510248
5tom20459george12489
6robert14523glen4568812354
7robert12365larry9999
8robert10248
9george12489
10glen45688
11larry9999
12glen12354
13
14
15
16
17
18
19
20
Sheet1


Then you add more raw data like this:


Excel 2007
ABCDEFGHIJKL
1NameSalesNamesSales1Sales2Sales3
2mike12389mike1238910254
3mike10254tom1165820459
4tom11658robert145231236510248
5tom20459george12489
6robert14523glen4568812354
7robert12365larry9999
8robert10248
9george12489
10glen45688
11larry9999
12glen12354
13billandrew11111
14billandrew22222
15billandrew33333
16billandrew44444
17billandrew55555
18billandrew77777
19robert88888
20
Sheet1



And, run the macro again you will get this:


Excel 2007
ABCDEFGHIJKL
1NameSalesNamesSales1Sales2Sales3Sales4Sales5Sales6
2mike12389mike1238910254
3mike10254tom1165820459
4tom11658robert14523123651024888888
5tom20459george12489
6robert14523glen4568812354
7robert12365larry9999
8robert10248billandrew111112222233333444445555577777
9george12489
10glen45688
11larry9999
12glen12354
13billandrew11111
14billandrew22222
15billandrew33333
16billandrew44444
17billandrew55555
18billandrew77777
19robert88888
20
Sheet1



See my next reply for the macro code, and, instructions.
 
Upvote 0
billandrew,

If you want the macro code to run in a specific worksheet name, then, I can adjust the macro accordingly.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub ReorganizeData()
' hiker95, 06/18/2017, ME1010271
Dim c As Range, rng As Range, v As Variant, o As Variant
Dim i As Long, n As Long, lc As Long, luc As Long
Application.ScreenUpdating = False
luc = Cells(1, Columns.Count).End(xlToLeft).Column
If luc > 4 Then Columns(4).Resize(, luc - 3).ClearContents
Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim o(1 To rng.Count, 1 To Columns.Count)
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In rng
    If Not .Exists(c.Value) Then
      n = n + 1
      .Add c.Value, Array(n, 2)
      o(n, 1) = c.Value: o(n, 2) = c.Offset(, 1).Value
    Else
      v = .Item(c.Value)
      v(1) = v(1) + 1
      o(v(0), v(1)) = c.Offset(, 1)
      .Item(c.Value) = v
      i = Application.Max(v(1), i)
    End If
  Next
  Range("E2").Resize(.Count, i).Value = o
  Range("E1") = "Names"
  lc = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  With Range(Cells(1, 6), Cells(1, lc))
    .Formula = "=""Sales"" & Column() - 5"
    .Value = .Value
  End With
End With
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorganizeData macro.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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