# Macro to insert helper column and formula



## ellison (Dec 18, 2022)

Hi, we are trying to tidy up data off reports that we receive and there's a repititive task that I'm wondering if a macro could help with...

We have Raw Data on Sheet 1. 

The data is is organised by column.  It can sometimes be numbers, blanks, alphanumeric, a to z, A to Z, contains duplicates, differing LEN's etc.  

(It really is a jumble - & hence why we are working on tidying it up!)

What we'd like to do is add a series of helper columns which will make it easier to feed into one of the other reports...

The helper columns would check if the "data cell" is blank, and if it is put the row number. EG "2"

And if the cells contains info, it would concatenate an underscore followed by the row number.EG "_2"

So for example if the formula entered in cell K2 is checking the info in cell J2:

#=IF(ISBLANK(J2),ROW(J2),CONCATENATE("_",ROW(J2)))

So as as cell J2 has info in it, it returns: "_2".  (If it was blank, it would return "2").

mrexcel-02.xlsxABCDEFGHIJKLMNO1Sheet1-Raw-Dataline-IDData-Col-01Data-Col-02Data-Col-03Results-Sheetline-IDData-Col-01Helper-01Data-Col-02Helper-02Data-Col-03Helper-0322eachchoicea2each_2choice_2a_233d3d_33344caroption4car_4option_4455bus5bus_55566bus6bus_66677aconfirm7a_7confirm_7788383_888992192_91_99101011101_10101_10Sheet1Cell FormulasRangeFormulaK2:K10,O2:O10,M2:M10K2=IF(ISBLANK(J2),ROW(J2),CONCATENATE("_",ROW(J2)))



The files we are working on have (currently) 110,000 rows of data and 35-ish columns.

Is there a way that a macro could:
i) generate a results sheet
ii) insert helper columns
iii) insert the helper formula (or something similar that would get the same result).

Thanks for any help you could provide. My fingers would be eternally grateful if they could avoid all of the copying, pasting and inserting columns that we are currently driving ourselves crazy with  

All the best from a very chilly UK!


----------



## Peter_SSs (Dec 18, 2022)

See if this is headed in the right direction. I have assumed that the heading "line-ID" is actually in cell A1 of "Sheet1".
Test with a copy of your workbook.


```
Sub AddHelpers()
  Dim rws As Long, c As Long
  Dim LineID As String
  
  Sheets("Sheet1").Copy After:=Sheets("Sheet1")
  With Sheets(Sheets("Sheet1").Index + 1)
    .Name = "Results"
    rws = .Range("A" & Rows.Count).End(xlUp).Row - 1
    LineID = "A2:A" & rws + 1
    For c = .Cells(1, Columns.Count).End(xlToLeft).Column To 2 Step -1
      Columns(c + 1).Insert
      With .Cells(2, c).Resize(rws)
        .Offset(, 1).Value = Evaluate("if(" & .Address & "="""","""",""_"")&" & LineID)
      End With
    Next c
  End With
End Sub
```

Here is my Sheet1

ellison.xlsmABCD1line-IDData-Col-01Data-Col-02Data-Col-0322eachchoicea33d44caroption55bus66bus77aconfirm8839921101011Sheet1


Results sheet created by the code above.

ellison.xlsmABCDEFG1line-IDData-Col-01Data-Col-02Data-Col-0322each_2choice_2a_233d_33344car_4option_4455bus_55566bus_66677a_7confirm_77883_888992_91_9910101_10101_10Results


----------



## Trebor76 (Dec 18, 2022)

Hi ellison,

See how this goes:


```
Option Explicit
Sub Macro1()

    Dim strResultsSheetName As String
    Dim wsSrc As Worksheet
    Dim wsResults As Worksheet
    Dim varItem As Variant
    Dim i As Long, j As Long
    
    Application.ScreenUpdating = False
    
    Set wsSrc = ThisWorkbook.Sheets("Sheet1") '<-Sheet name containing raw data.
    strResultsSheetName = "Results" '<-Sheet name for formatted raw data.
    
    On Error Resume Next
        Set wsResults = ThisWorkbook.Sheets(strResultsSheetName)
        If Err.Number <> 0 Then
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = strResultsSheetName
            Set wsResults = ThisWorkbook.Sheets(strResultsSheetName)
            For Each varItem In Array("line-ID", "Data-Col-01", "Helper-01", "Data-Col-02", "Helper-02", "Data-Col-03", "Helper-03") 'Put headings in
                wsResults.Range("A1").Offset(0, i).Value = varItem
                i = i + 1
            Next varItem
        Else
            i = 2
            j = wsResults.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If j >= i Then
                wsResults.Rows(i & ":" & j).Clear
            End If
        End If
    On Error GoTo 0
    
    i = 2
    j = wsSrc.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    wsSrc.Range("A" & i & ":B" & j).Copy Destination:=wsResults.Range("A" & i)
    wsResults.Range("C" & i & ":C" & j).Formula = "=IF(ISBLANK(B2),ROW(B2),CONCATENATE(""_"",ROW(B2)))"
    wsSrc.Range("C" & i & ":C" & j).Copy Destination:=wsResults.Range("D" & i)
    wsResults.Range("E" & i & ":E" & j).Formula = "=IF(ISBLANK(D2),ROW(D2),CONCATENATE(""_"",ROW(D2)))"
    wsSrc.Range("D" & i & ":D" & j).Copy Destination:=wsResults.Range("F" & i)
    wsResults.Range("G" & i & ":G" & j).Formula = "=IF(ISBLANK(F2),ROW(F2),CONCATENATE(""_"",ROW(F2)))"
    
    Application.ScreenUpdating = True

End Sub
```

Regards,

Robert


----------



## ellison (Dec 19, 2022)

WOW what has worked beautifully, huge thanks to you - really appreciated!!!!


----------



## ellison (Dec 19, 2022)

H, in fact both of those options work beautifully thanks...

Apart from 1 slight (but fairly crucial!) error which is on *my* part, where I made an assumption which has turned out to be wrong.... sorry!

I assumed that when Excel did the subsequent sort, it would be able to keep the line ID's that begin with the underscore in order.   It does, but once Excel goes over roughly 50K rows, it doesn't stick with the "numerical order". (I'm kicking myself now because it's treating it like text, sorry).

So a quick example of the order that it's assigning where for example _410 should be top of the list, _5008 second on the list etc

wip-07-READY-TO-EXPORT-post-macro.xlsxC104365_24274104366_31487104367_32050104368_32612104369_3840104370_3841104371_3897104372_3898104373_410104374_43681104375_44099104376_44100104377_44101104378_49129104379_5008104380_50791104381_51389104382_52491Results

Would you be able to suggest something?

Thanks
Neil


----------



## Trebor76 (Dec 19, 2022)

You could have another helper column that extracts the number using a formula like this...

=VALUE(SUBSTITUTE(A1,"_",""))

...and then sort on that column.


----------



## Peter_SSs (Dec 19, 2022)

I'm not sure that you have given us enough information about exactly what is happening. 
In your example, *all *the values have an underscore whereas in the original samples there tended to be a mixture.
In any case, if my suggestion worked for you as well, then can't you just use column A to 'sort by' since my code simply grabbed the numbers from there? What I'm suggesting is really the same as @Trebor76 but without the need to re-generate the original numbers.


----------



## ellison (Dec 20, 2022)

Sorry guys, you're absolutely right - I need to go back and double check some more details about an overview of this - before diving headlong into the detail.

Your info is really appreciated.

And your suggested macro's work just fine, so I'll leave that marked up as a solution.

Hope to be back to you when I've got a bit more of an overview on this....


----------



## ellison (Dec 20, 2022)

Thanks for all of your help guys....

In the end, I tweaked the concatenate from an underscore to a minus sign.

That enabled Excel to still treat the cell as "numerical" and therefore put them into the designated order.

(The reason that this is being done is to tidy up the data before it's added into the system. By marrying the updated data up into the same "order" as the old data, they can apparently veriy any glitches quicker).

But the long and short of it is: huge thanks for all of your help. And I think I've found a macro (or macros!) which will add in the appropriate helper columns. And those helper columns can be used to quickly sort the data into order based a combination of the line-ID and whether or not it's blank.

Have a great Holiday Season!

🎅


----------



## Peter_SSs (Dec 20, 2022)

Glad you got it sorted.  
Thanks for letting us know.


----------

