Excel trick for this task

Question

I've got a spreadsheet in excel with this rows:

       COLUMN
Value1.Value2.Value3
Value4.Value5.Value6
Value7.Value8.Value9

In another spreadsheet I've got a simple list with names:

   COLUMN
    Name1
    Name2
    Name3

And,of course, this list is huge :).

So need to have the following spreasdsheet at the end:

       COLUMN

Value1.Name1.Value2.Value3
Value4.Name1.Value5.Value6
Value7.Name1.Value8.Value9
Value1.Name2.Value2.Value3
Value4.Name2.Value5.Value6
Value7.Name2.Value8.Value9
Value1.Name3.Value2.Value3
Value4.Name4.Value5.Value6
Value7.Name4.Value8.Value9

I have to concatenate the names on the list with all the values on spreadsheet replicating them for ALL the names.

Is there a way of doing this process automatically? The manual process would take hours to be done and I think there's a smarter way of doing that although I don't know it! :)

Thanks in advance for your help.

1) Do you need to add `NameI` always after first `.` in `value` column? 2) Are there duplicates in `Name` Column 3) should it be VBA solution or formula? — Dmitry Pavliv, Feb 24 '14 at 13:04
Dalek, are all your values in column `Values` all valid? I mean do they always has `.`? — Dmitry Pavliv, Feb 24 '14 at 13:19
There is always a "." between the values. the values change but there is always a "." between them. — D. Caan, Feb 24 '14 at 13:21

score 2 · Answer 1 · answered Feb 24 '14 at 15:14

2

And it is a good challenge to do it with formulas: :)

enter image description here

With this array formula in D1 and then copy down

=INDEX(LEFT($A$1:$A$4;FIND(".";$A$1:$A$4))&TRANSPOSE($C$1:$C$3)&RIGHT($A$1:$A$4;LEN($A$1:$A$4)-FIND(".";$A$1:$A$4)+1);1+INT((ROWS($D$1:D1)-1)/ROWS($C$1:$C$3));1+MOD(ROWS($D$1:D1)-1;ROWS($C$1:$C$3)))

Depending on your regional settings you may need to replace field separator ";" by ","

answered Feb 24 '14 at 15:14

CRondao

1,883
2
12
10

As someone who shrinks away from the likes of the Excel Hero Formula Challenges, I still must say that this is some ***serious*** eye-candy. **+1!** – WGS Feb 24 '14 at 15:22
Copying down doesn't work for me. For the first row, everything is fine. Any special config for this? – D. Caan Feb 25 '14 at 13:57
If it works for the first row, probably you have a relative reference that should be absolute or vice verse. I tried again and it works. Make sure references are exactly equal. No special config. Or show me the formula you're using. (Thinking twice, did you Ctrl Shift Enter to enter the formula? it is an array formula) – CRondao Feb 25 '14 at 15:57

Dmitry Pavliv · Accepted Answer · 2014-02-24T14:52:54.870

There is always a "." between the values.

Try this code. Using arrays would be much faster for huge list of names/values:

Sub test()
    Dim arrVal As Variant
    Dim arrNames As Variant
    Dim arrRes As Variant
    Dim v, n, k As Long

    'change Sheet1 to suit
    With ThisWorkbook.Worksheets("Sheet1")
        'change A1:A3 to values address
        arrVal = .Range("A1:A3")
        'change B1:B3 to names address
        arrNames = .Range("B1:B3")

        ReDim arrRes(1 To UBound(arrVal) * UBound(arrNames), 1 To 1)
        k = 1
        For Each v In arrVal
            For Each n In arrNames
                arrRes(k, 1) = Left(v, InStr(1, v, ".")) & n & Mid(v, InStr(1, v, "."))
                k = k + 1
            Next
        Next v

        'change "c1" to start cell where to put new values
        .Range("C1").Resize(UBound(arrRes, 1)) = arrRes
    End With
End Sub

Note: If you don't know exact addresses of "values" and "name" ranges, change this part

'change A1:A3 to values address
arrVal = .Range("A1:A3")
'change B1:B3 to names address
arrNames = .Range("B1:B3")

to

'change A1:A to "values" address
arrVal = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
'change B1:B to "names" address
arrNames = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)

In that case "values" and "name" ranges starts from A1 and B1 accordingly and ends in the last non empty row in coumns A and B accordingly.

Result:

enter image description here

I don't know if I've done something wrong but it's giving me the error "Type mismatch" on .Range("C1").Resize(UBound(arrRes)) = WorksheetFunction.Transpose(arrRes) — D. Caan, Feb 24 '14 at 13:34
as follows up from comments to your Q, this code assumes that all values in array `arrVal = .Range("A1:A3")` has `.`. Is it true? maybe you've included column headers in address of "value" range? — Dmitry Pavliv, Feb 24 '14 at 13:37
@Dalek, there was some limitations with Transpose function (limit for array lenght). I've updated my answer - now it should work — Dmitry Pavliv, Feb 24 '14 at 14:39

score 0 · Answer 3 · answered Feb 24 '14 at 13:21

I think that could work.

    Const FIRST_TALBE = 4
    Const SECOND_TABLE = 2

    Sub makeTheJob()
        For i = 1 To lastRow
            l = Split(Cells(i, FIRST_TABLE), ".")
            newvalue = l(0) & "." & Cells(i, SECOND_TABLE) & "." & l(1) & "." & l(2)
            Debug.Print newvalue
        Next i
    End Sub

Excel trick for this task

3 Answers3