Thanks:  0
Likes:  0

1. ## Extract a BLANK

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.

 Name Sales mike 12389 mike 10254 tom 11658 tom 20459 robert 14523 robert 12365 robert 10248 george 12489 glen 45688 larry 9999 glen 12354

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

2. ## Re: Extract a BLANK

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.

3. ## Re: Extract a BLANK

You don't show what columns your data is in, but maybe this...
=if(A1="","",your-formula)

4. ## Re: Extract a BLANK

The data is in the first table on the top.

5. ## Re: Extract a BLANK

OK the method stays the same, just adjust the A1 reference to suite your data

6. ## Re: Extract a BLANK

thank you for the help. I was adding the second if in the incorrect location.

7. ## Re: Extract a BLANK

No problem, happy to help

8. ## Re: Extract a BLANK

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.

9. ## Re: Extract a BLANK

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
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
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.

works great.

Thank you

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•