Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Extract a BLANK

  1. #1
    Board Regular
    Join Date
    Mar 2014
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Jun 2011
    Posts
    1,145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Excel 2010, Windows 7

  3. #3
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,994
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract a BLANK

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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract a BLANK

    The data is in the first table on the top.

  5. #5
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,994
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract a BLANK

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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  6. #6
    Board Regular
    Join Date
    Mar 2014
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract a BLANK

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

  7. #7
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,994
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract a BLANK

    No problem, happy to help

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  8. #8
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,084
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default 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.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,084
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    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.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  10. #10
    Board Regular
    Join Date
    Mar 2014
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract a BLANK

    works great.

    Thank you

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.