Concatenate a large string

[Nomas]

Hi,

New to excel and have been working on an issue and making progress. I am looking to populate a macro with a concatenate formula but it appears too large to run in the formula. The working code is this

Sub FillCalc_down()

Sheets("Item Upload Template").Range("A3").Formula = "=IF(CONCATENATE('Item List'!A2,'Item List'!B2,'Item List'!C2,'Item List'!D2)=CONCATENATE('Item List Checksheet'!A2,'Item List Checksheet'!B2,'Item List Checksheet'!C2,'Item List Checksheet'!D2),""No Change"",""Upload"")"
Sheets("Item Upload Template").Select
Sheets("Item Upload Template").Range("A3:BY3").AutoFill Destination:=Range("A3:BY12000")

End Sub

The problem is I need to compare the range from A3:BY3 (77columns each) on each of these sheets (and each CONCATENATE) and if I put that it the macro script turns red and wont run because it run onto another line and states Compile Error: Expected end of Statement. Basically it seems there is a limit to the line width of the editor and dropping it on a new line creates a new statement. Any guidance is appreciated!

 

[p45cal]

split the lines with
space + underline + return:

Sheets("Item Upload Template").Range("A3").Formula = _
    "=IF(CONCATENATE('Item List'!A2,'Item List'!B2,'Item List'!C2,'Item List'!D2)=CONCATENATE('Item List Checksheet'!A2,'Item List Checksheet'!B2,'Item List Checksheet'!C2,'Item List Checksheet'!D2),""No Change"",""Upload"")"
Sheets("Item Upload Template").Select
Sheets("Item Upload Template").Range("A3:BY3").AutoFill _
    Destination:=Range("A3:BY12000")

 

[Nomas]

Thanks p45,

I am not sure if I am not doing that right or if it cant be done, here is what one of the CONCATENATE strings does, its just too long and runs onto the other line. I'm guessing I am going to have to find a way to run a bunch of CONCATENATEs together in order to gerry rig this thing to work. It you see what I am doing wrong let me know. I really appreciate the help

Sheets("Item Upload Template").Range("A3").Formula = "=IF(CONCATENATE('Item List'!A2,'Item List'!B2,'Item List'!C2,'Item List'!D2,'Item List'!E2,'Item List'!F2,'Item List'!G2,'Item List'!H2,'Item List'!I2,'Item List'!J2,'Item List'!K2,'Item List'!L2,'Item List'!M2,'Item List'!N2,'Item List'!O2,'Item List'!P2,'Item List'!Q2,'Item List'!R2,'Item List'!S2,'Item List'!T2,'Item List'!U2,'Item List'!V2,'Item List'!W2,'Item List'!X2,'Item List'!Y2,'Item List'!Z2,'Item List'!AA2,'Item List'!AB2,'Item List'!AC2,'Item List'!AD2,'Item List'!AE2,'Item List'!AF2,'Item List'!AG2,'Item List'!AH2,'Item List'!AI2,'Item List'!AJ2,'Item List'!AK2,'Item List'!AL2,'Item List'!AM2,'Item List'!AN2,'Item List'!AO2,'Item List'!AP2,'Item List'!AQ2,'Item List'!AR2,'Item List'!AS2,'Item List'!AT2,'Item List'!AU2,'Item List'!AV2,'Item List'!AW2,'Item List'!AX2,'Item List'!AY2,'Item List'!AZ2,'Item List'!BA2,'Item List'!BB2,'Item List'!BC2,'Item List'!BD2,'Item List'!BE2,'Item List'!BF2,'Item List'!BG2,'Item List'!BH2,'Item List'!BI2,'Item List'!BJ2,'Item List'!BK2,'Item List'!BL2,'Item List'!BM2,'Item List '!BN2,'Item List'!BO2,'Item List'!BP2,'Item List'!BQ2,'Item List'!BR2,'Item List'!BS2,'Item List'!BT2,'Item List'!BU2,'Item List'!BV2,'Item List'!BW2,'Item List'!BX2,'Item List'!BY2)=CONCATENATE('Item List Checksheet'!A2,'Item List Checksheet'!B2,'Item List Checksheet'!C2,'Item List Checksheet'!D2),""No Change"",""Upload"")"
Sheets("Item Upload Template").Select
Sheets("Item Upload Template").Range("A3:BY3").AutoFill Destination:=Range("A3:BY12000")

 

[Scott Huish]

Why don't you use the macro to concatenate the values rather than a formula?

But why do you need to concatenate anything? It looks like all your are trying to do is see if two columns match. Is this correct?

 

[p45cal]

HOTPEPPER is quite right, it might be better to do this entirely in vba rather than using mega-formulae. Sticking with your method of vba putting formulae on the sheet, I discover that excel does not like to split a formula string in the code with only continuation characters, so try:

FormulaPart1 = "=IF(CONCATENATE('Item List'!A2,'Item List'!B2," & _
"'Item List'!C2,'Item List'!D2,'Item List'!E2,'Item List'!F2,'Item List'!G2,'Item List'!H2,'Item List'!I2," & _
"'Item List'!J2,'Item List'!K2,'Item List'!L2,'Item List'!M2,'Item List'!N2,'Item List'!O2,'Item List'!P2," & _
"'Item List'!Q2,'Item List'!R2,'Item List'!S2,'Item List'!T2,'Item List'!U2,'Item List'!V2,'Item List'!W2," & _
"'Item List'!X2,'Item List'!Y2,'Item List'!Z2,'Item List'!AA2,'Item List'!AB2,'Item List'!AC2,'Item List'!AD2," & _
"'Item List'!AE2,'Item List'!AF2,'Item List'!AG2,'Item List'!AH2,'Item List'!AI2,'Item List'!AJ2," & _
"'Item List'!AK2,'Item List'!AL2,'Item List'!AM2,'Item List'!AN2,'Item List'!AO2,'Item List'!AP2," & _
"'Item List'!AQ2,'Item List'!AR2,'Item List'!AS2,'Item List'!AT2,'Item List'!AU2,'Item List'!AV2," & _
"'Item List'!AW2,'Item List'!AX2,'Item List'!AY2,'Item List'!AZ2,'Item List'!BA2,'Item List'!BB2," & _
"'Item List'!BC2,'Item List'!BD2,'Item List'!BE2,'Item List'!BF2,'Item List'!BG2,'Item List'!BH2," & _
"'Item List'!BI2,'Item List'!BJ2,'Item List'!BK2,'Item List'!BL2,'Item List'!BM2,'Item List'!BN2," & _
"'Item List'!BO2,'Item List'!BP2,'Item List'!BQ2,'Item List'!BR2,'Item List'!BS2,'Item List'!BT2," & _
"'Item List'!BU2,'Item List'!BV2,'Item List'!BW2,'Item List'!BX2,'Item List'!BY2)"
FormulaPart2 = "=CONCATENATE(" & _
"'Item ListChecksheet'!A2,'Item ListChecksheet'!B2,'Item ListChecksheet'!C2," & _
"'Item ListChecksheet'!D2,'Item ListChecksheet'!E2,'Item ListChecksheet'!F2,'Item ListChecksheet'!G2,'Item ListChecksheet'!H2,'Item ListChecksheet'!I2," & _
"'Item ListChecksheet'!J2,'Item ListChecksheet'!K2,'Item ListChecksheet'!L2,'Item ListChecksheet'!M2,'Item ListChecksheet'!N2,'Item ListChecksheet'!O2,'Item ListChecksheet'!P2," & _
"'Item ListChecksheet'!Q2,'Item ListChecksheet'!R2,'Item ListChecksheet'!S2,'Item ListChecksheet'!T2,'Item ListChecksheet'!U2,'Item ListChecksheet'!V2,'Item ListChecksheet'!W2," & _
"'Item ListChecksheet'!X2,'Item ListChecksheet'!Y2,'Item ListChecksheet'!Z2,'Item ListChecksheet'!AA2,'Item ListChecksheet'!AB2,'Item ListChecksheet'!AC2,'Item ListChecksheet'!AD2," & _
"'Item ListChecksheet'!AE2,'Item ListChecksheet'!AF2,'Item ListChecksheet'!AG2,'Item ListChecksheet'!AH2,'Item ListChecksheet'!AI2,'Item ListChecksheet'!AJ2," & _
"'Item ListChecksheet'!AK2,'Item ListChecksheet'!AL2,'Item ListChecksheet'!AM2,'Item ListChecksheet'!AN2,'Item ListChecksheet'!AO2,'Item ListChecksheet'!AP2," & _
"'Item ListChecksheet'!AQ2,'Item ListChecksheet'!AR2,'Item ListChecksheet'!AS2,'Item ListChecksheet'!AT2,'Item ListChecksheet'!AU2,'Item ListChecksheet'!AV2," & _
"'Item ListChecksheet'!AW2,'Item ListChecksheet'!AX2,'Item ListChecksheet'!AY2,'Item ListChecksheet'!AZ2,'Item ListChecksheet'!BA2,'Item ListChecksheet'!BB2," & _
"'Item ListChecksheet'!BC2,'Item ListChecksheet'!BD2,'Item ListChecksheet'!BE2,'Item ListChecksheet'!BF2,'Item ListChecksheet'!BG2,'Item ListChecksheet'!BH2," & _
"'Item ListChecksheet'!BI2,'Item ListChecksheet'!BJ2,'Item ListChecksheet'!BK2,'Item ListChecksheet'!BL2,'Item ListChecksheet'!BM2,'Item ListChecksheet'!BN2," & _
"'Item ListChecksheet'!BO2,'Item ListChecksheet'!BP2,'Item ListChecksheet'!BQ2,'Item ListChecksheet'!BR2,'Item ListChecksheet'!BS2,'Item ListChecksheet'!BT2," & _
"'Item ListChecksheet'!BU2,'Item ListChecksheet'!BV2,'Item ListChecksheet'!BW2,'Item ListChecksheet'!BX2,'Item ListChecksheet'!BY2),""No Change"",""Upload"")"
Sheets("Item Upload Template").Range("A3").Formula = FormulaPart1 & FormulaPart2
Sheets("Item Upload Template").Select
Sheets("Item Upload Template").Range("A3:BY3").AutoFill Destination:=Sheets("Item Upload Template").Range("A3:BY12")

where I've used both & and _ along with closing and opening quotation marks. I tested this in XL2010, but it may be too long for earlier versions (3233 characters).

 

[mikerickson]

Mixing the approaches, you could write a UDF to cocatenate ranges. There are several around this forum, but a simple one would look like

Function ConcatRange(aRange as Range, Delimiter As String) As String
    Dim oneCell as Range
    For each oneCell in Range
        ConcatRange = ConcatRange & Delimiter & CStr(oneCell.Value)
    Next oneCell
    ConcatRange = Mid(ConcatRange, Len(Delimiter) + 1)
End Function

Then use that fuction to put a formula in a cell like

Sheets("Item Upload Template").Range("A3").Formula = "=IF(ConcatRange('Item List'!A2:D2)=ConcatRange('Item List Checksheet'!A2:D2),""No Change"",""Upload"")"

 

[Fazza]

It seems there are two ranges to compare. What about in a third range of the same size, set up formulas that check each cell. Such as =ref1!A1=ref2!A1. Or instead of returning TRUE/FALSE you might prefer to return numeric values, so change the formula to =(ref1!A1=ref2!A1)*1

Now check this third range. Such as in an extra column.

HTH

 

[Nomas]

Wow, thanks for all the responses. I ll try to address all in order. A couple things of note there is a macro that runs upon opening the workbook that duplicates the item list and calls it item list checksheet. This runs fine with no problem. However when I paste in your formula (p45) it prompts me several times to look for the file that I want to direct the query to. If I cancel out of these, it puts the right formula in the right cell, but I get a #REF! error in all cells, if I just enter the formula in a cell by hand it works. It seems like the copy sheet rename is getting caught up in the paste/fill down macro because its taking so long to run the fill down.

I tried the other formula as a mix but dont think I am doing it right as I input the following

Sub FillCalc_down()


Function ConcatRange(aRange As Range, Delimiter As String) As String
Dim oneCell As Range
For Each oneCell In Range
ConcatRange = ConcatRange & Delimiter & CStr(oneCell.Value)
Next oneCell
ConcatRange = Mid(ConcatRange, Len(Delimiter) + 1)
End Function
Sheets("Item Upload Template").Range("A3").Formula = "=IF(ConcatRange('Item List'!A2:BY2)=ConcatRange('Item List Checksheet'!A2:BY2),""No Change"",""Upload"")"
Sheets("Item Upload Template").Select
Sheets("Item Upload Template").Range("A3:BY3").AutoFill Destination:=Sheets("Item Upload Template").Range("A3:BY12000")


End Function

End Sub


It runs into an error looking for and End Sub prior to getting to the Function tab.



Fazza I am not sure what cells the 3rd range would check, the differential between the 2 ranges or some other range?


I cant tell you how much I really appreciate all the help, its awesome. If I can get this tool built it will help out a project I am working on immensely. Any guidance on this last part I feel like its so close to working

 

[Nomas]

Actually, P45 your's worked like a gem, I noticed that my sheet had a space in it, and your macro for the formula removed the space from Item List Checksheet (Item ListChecksheet). I fixed and it runs like a charm. To everyone THANK YOU SOOO MUCH. This rocks! I'm sure I will have more questions but I appreciate everyones help!