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!

Unique ID		Cost Center		Unique ID	Cost Center
12345		98745617		12345	98745617
67891		98756562		12345	98756562
23456		98765632		12345	98765632
78912		98786464		12345	98786464
				67891	98745617
				67891	98756562
				67891	98765632
				67891	98786464
				23456	98745617
				23456	98756562
				23456	98765632
				23456	98786464
				78912	98745617
				78912	98756562
				78912	98765632
				78912	98786464

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

 

[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