Macro to insert helper column and formula

[ellison]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Sheet1-Raw-Data	line-ID	Data-Col-01	Data-Col-02	Data-Col-03			Results-Sheet	line-ID	Data-Col-01	Helper-01	Data-Col-02	Helper-02	Data-Col-03	Helper-03
2		2	each	choice	a				2	each	_2	choice	_2	a	_2
3		3	d						3	d	_3		3		3
4		4	car	option					4	car	_4	option	_4		4
5		5	bus						5	bus	_5		5		5
6		6	bus						6	bus	_6		6		6
7		7	a	confirm					7	a	_7	confirm	_7		7
8		8	3						8	3	_8		8		8
9		9	2	1					9	2	_9	1	_9		9
10		10	1		1				10	1	_10		10	1	_10
Sheet1

Cell Formulas
Range	Formula
K2:K10,O2:O10,M2:M10	K2	=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]

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.xlsm
	A	B	C	D
1	line-ID	Data-Col-01	Data-Col-02	Data-Col-03
2	2	each	choice	a
3	3	d
4	4	car	option
5	5	bus
6	6	bus
7	7	a	confirm
8	8	3
9	9	2	1
10	10	1		1
Sheet1

Results sheet created by the code above.

ellison.xlsm
	A	B	C	D	E	F	G
1	line-ID	Data-Col-01		Data-Col-02		Data-Col-03
2	2	each	_2	choice	_2	a	_2
3	3	d	_3		3		3
4	4	car	_4	option	_4		4
5	5	bus	_5		5		5
6	6	bus	_6		6		6
7	7	a	_7	confirm	_7		7
8	8	3	_8		8		8
9	9	2	_9	1	_9		9
10	10	1	_10		10	1	_10
Results

 

[Trebor76]

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]

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

 

[ellison]

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.xlsx
	C
104365	_24274
104366	_31487
104367	_32050
104368	_32612
104369	_3840
104370	_3841
104371	_3897
104372	_3898
104373	_410
104374	_43681
104375	_44099
104376	_44100
104377	_44101
104378	_49129
104379	_5008
104380	_50791
104381	_51389
104382	_52491
Results

Would you be able to suggest something?

Thanks
Neil

 

[Trebor76]

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]

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]

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]

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]

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