Excel - How to distribute data in Excel using Excel functions

[Dasarath]

Hello All,
I am trying to create a excel formula(index and ceiling functions)for below mention data.Everyday I have to do it manually please can anybody me to sort this problem.

Thanks in Advance


Here is the Data table,Each code should got to single name.Codes and Names are dynamic

Code	COUNTIF	Names	Names
SOO12	2	X	X
SOO12	2	Y	X
SOO2	2	Z	Y
SOO2	2		Y
SOW6	3		Z
SOW6	3		Z
SOW6	3		Z
SOW7	4		X
SOW7	4		X
SOW7	4		X
SOW7	4		X

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[mse330]

Welcome to the forums Dasarath

If this is the current data set that you have, what would be the desired output you are looking for ? Please give an example

 

[Dasarath]

Column4 is the desired output

 

[Dasarath]

Names,last column will be the output. Thank you

 

[Dasarath]

Hi,
I will get a rawdata like below table.

Code	Names
SOO12	X
SOO12	Y
SOO2	Z
SOO2
SOW6
SOW6
SOW6
SOW7
SOW7
SOW7
SOW7
SOX11
SOX11
YOX4
YOX4
YOX4
YOX4
YOX4
YOX4
YOX4
YOX1

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Once i get this data i apply Countif function to get the each Code Count. Then i will sort the data with Code Column.

Here starts my real work ,In ColumnC We have to apply formula ,where in ColumnA Code:SOO12 is repeated 2 times this will assign to Name:X 2times ,Code:SOO2 will assign to2times to Name:Y and Code:SOW6 is repeated 3times & will assign to Z 3times .Now 3 Names are completed ,next code will take X and so on..and output should look like ColumnE ,This is how i do manually everyday .Everyday Rawdata will be around 5000Codes repeated.

Finally output should be like below table:

Code	COUNTIF	Output
SOO12	2	X
SOO12	2	X
SOO2	2	Y
SOO2	2	Y
SOW6	3	Z
SOW6	3	Z
SOW6	3	Z
SOW7	4	X
SOW7	4	X
SOW7	4	X
SOW7	4	X
SOX11	2	Y
SOX11	2	Y
YOX4	7	Z
YOX4	7	Z
YOX4	7	Z
YOX4	7	Z
YOX4	7	Z
YOX4	7	Z
YOX4	7	Z
YOX5	1	X

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

Please help me out with the formula,thank you.

 

[mse330]

Sorry Dasarath I got so busy at work I couldn't get back to you earlier. You can use the below VBA code assuming you have your "Codes" in column A and your "Names" in column B & your header is in row # 1 & your data starts in row # 2 ... Enjoy

Sub test()

Dim strCode As String, strName As String

Dim CodesLastRow As Single, NamesLastRow As Single
CodesLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
NamesLastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

Dim x As Single, y As Single

strCode = ActiveSheet.Range("A2").Value
y = 2

For x = 2 To CodesLastRow
    If ActiveSheet.Range("A" & x).Value = strCode Then
        ActiveSheet.Range("C" & x).Value = ActiveSheet.Range("B" & y).Value
    Else
        If y + 1 <= NamesLastRow Then
            y = y + 1
            strCode = ActiveSheet.Range("A" & x).Value
            ActiveSheet.Range("C" & x).Value = ActiveSheet.Range("B" & y).Value
        Else
            y = 2
            strCode = ActiveSheet.Range("A" & x).Value
            ActiveSheet.Range("C" & x).Value = ActiveSheet.Range("B" & y).Value
        End If
    End If
Next x
    
MsgBox "Task completed for all " & CodesLastRow & " records :)", vbInformation

End Sub

 

[Dasarath]

Thank you so much for your time this is what i am looking for working like a charm ,Once again million thanks .

 

[mse330]

Great ! You are most welcome