Create multiple values based on two existing data sets.

[Andy_Morland]

Hi


So I currently have two sets of data, per below (unique ID and cost center).

Where I need to get to is to have a separate row of data for each unique ID and cost center (e.g. 4 unique ID's x 4 cost centers = 16 rows of data).

I feel like I'm missing something really simple here - and I don't think what I've put in the title describes what I'm after too well.


Thanks in advance!


[TABLE="class: grid, width: 426"]
<tbody>[TR]
[TD="align: center"]Unique ID[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cost Center[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Unique ID[/TD]
[TD="align: center"]Cost Center[/TD]
[/TR]
[TR]
[TD="align: center"]12345[/TD]
[TD="align: center"][/TD]
[TD="align: center"]98745617[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12345[/TD]
[TD="align: center"]98745617[/TD]
[/TR]
[TR]
[TD="align: center"]67891[/TD]
[TD="align: center"][/TD]
[TD="align: center"]98756562[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12345[/TD]
[TD="align: center"]98756562[/TD]
[/TR]
[TR]
[TD="align: center"]23456[/TD]
[TD="align: center"][/TD]
[TD="align: center"]98765632[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12345[/TD]
[TD="align: center"]98765632[/TD]
[/TR]
[TR]
[TD="align: center"]78912[/TD]
[TD="align: center"][/TD]
[TD="align: center"]98786464[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12345[/TD]
[TD="align: center"]98786464[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]67891[/TD]
[TD="align: center"]98745617[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]67891[/TD]
[TD="align: center"]98756562[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]67891[/TD]
[TD="align: center"]98765632[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]67891[/TD]
[TD="align: center"]98786464[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]23456[/TD]
[TD="align: center"]98745617[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]23456[/TD]
[TD="align: center"]98756562[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]23456[/TD]
[TD="align: center"]98765632[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]23456[/TD]
[TD="align: center"]98786464[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]78912[/TD]
[TD="align: center"]98745617[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]78912[/TD]
[TD="align: center"]98756562[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]78912[/TD]
[TD="align: center"]98765632[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]78912[/TD]
[TD="align: center"]98786464[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]

 

[D3allamerican07]

Andy,

Are the two far right columns what you are looking to produce through a macro?

Bill

 

[NARAYANK991]

Hi ,

The list of unique IDs can be obtained by :

=INDEX(Unique_ID, INT((ROW(A1)-1)/ROWS(Cost_Center))+1)

Enter this in the first output row , and copy down.

The corresponding cost centers can be obtained by :

=INDEX(Cost_Center, MOD((ROW(A1)-1),ROWS(Cost_Center))+1)

Enter this in the first output row , and copy down.

Replace the names Unique_ID and Cost_Center with the absolute references for your data range.

 

[D3allamerican07]

Andy,

If my above question is correct, this simple macro will output what you are looking for.

Sub Test()

    ActiveSheet.Range("C1").Value = "Unique ID"
    ActiveSheet.Range("D1").Value = "Cost Center"
    
    idLR = Range("A" & Rows.Count).End(xlUp).Row
    ccLR = Range("A" & Rows.Count).End(xlUp).Row
    
    Set idRng = ActiveSheet.Range("A2:A" & idLR)
    
    j = 2
    
    For Each cell In idRng
        For i = 2 To ccLR
            ActiveSheet.Range("C" & j).Value = cell.Value
            ActiveSheet.Range("D" & j).Value = ActiveSheet.Range("B" & i).Value
            j = j + 1
        Next i
    Next cell
    
End Sub

Let me know if you have any questions.

Bill

 

[Fluff]

Another option is

Sub CopyMultiple()

    Dim CcRws As Long
    Dim Cl As Range
    
    CcRws = Range("B" & Rows.Count).End(xlUp).Row - 1
    For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(1 * CcRws) = Cl.Value
        Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(CcRws).Value = Range("B2:B" & CcRws + 1).Value
    Next Cl

End Sub