Need to Transpose these 1 to 6 columns into one single Column (multiple rows),Keeping the left 2 columns intact. Please help!

[Suman Mehta]

Company Name	Count of Names	1	2	3	4	5	6
Deco-Mica Ltd	4	Deco Mica Ltd	Deco-Mica Ltd	Deco Mica	Deco-Mica
Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing Industries Ltd	Bharat Textiles & Proofing	Bharat Textiles &	Bharat Textiles
Sainik Finance & Industries Limited	4	Sainik Finance & Industries Limited	Sainik Finance & Industries	Sainik Finance & Industri	Sainik Finance &
G-Tech Info-Training Limited	5	G Tech Info Training Limited	G Tech Info Training	G-Tech Info-Training Limited	G Tech Info	G-Tech Info-Training
Kome-on Communication Ltd.	5	Kome on Communication Ltd	Kome-on Communication Ltd	Kome On Communication	Kome on Communication	Kome-On Communication
Associated Alcohol and Breweries Ltd	5	Associated Alcohol & Breweries Ltd	Associated Alcohols & Breweries	Associated Alcohol & Breweries	Associated Alcohol &	Associated Alcohols
Indo US Bio-Tech Ltd.	6	Indo US Bio Tech Ltd	Indo US Bio-Tech Ltd	Indo US Bio Tech	Indo US Bio-Tech	Indo US BioTech	Indo US Bio-Tech
Unick Fix-A-Form & Printers Ltd	6	Unick Fix A Form & Printers Ltd	Unick Fix A Form & Printers	Unick Fix A Form Printers	Unick Fix-A-Form & Printers	Unick Fix-A-Form Printers	Unick Fix A
CG-VAK Software & Exports Ltd.	6	CG VAK Software & Exports Ltd	CG VAK Software & Exports	CG Vak Software & Exports	CG-VAK Software & Exports	CG-Vak Software & Exports	CG VAK Software

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data & expected results.

Is this what you mean? If not please post the expected results for your sample data and explain how you get those results.

Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 8).Value
  ReDim b(1 To Application.Sum(Application.Index(a, 0, 2)), 1 To 3)
  For i = 2 To UBound(a)
    For j = 3 To 2 + a(i, 2)
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, j)
    Next j
  Next i
  Range("A" & Rows.Count).End(xlUp).Offset(3).Resize(k, 3).Value = b
End Sub

Suman Mehta 1.xlsm
	A	B	C	D	E	F	G	H
1	Company Name	Count of Names	1	2	3	4	5	6
2	Deco-Mica Ltd	4	Deco Mica Ltd	Deco-Mica Ltd	Deco Mica	Deco-Mica
3	Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing Industries Ltd	Bharat Textiles & Proofing	Bharat Textiles &	Bharat Textiles
4	Sainik Finance & Industries Limited	4	Sainik Finance & Industries Limited	Sainik Finance & Industries	Sainik Finance & Industri	Sainik Finance &
5	G-Tech Info-Training Limited	5	G Tech Info Training Limited	G Tech Info Training	G-Tech Info-Training Limited	G Tech Info	G-Tech Info-Training
6	Kome-on Communication Ltd.	5	Kome on Communication Ltd	Kome-on Communication Ltd	Kome On Communication	Kome on Communication	Kome-On Communication
7	Associated Alcohol and Breweries Ltd	5	Associated Alcohol & Breweries Ltd	Associated Alcohols & Breweries	Associated Alcohol & Breweries	Associated Alcohol &	Associated Alcohols
8	Indo US Bio-Tech Ltd.	6	Indo US Bio Tech Ltd	Indo US Bio-Tech Ltd	Indo US Bio Tech	Indo US Bio-Tech	Indo US BioTech	Indo US Bio-Tech
9	Unick Fix-A-Form & Printers Ltd	6	Unick Fix A Form & Printers Ltd	Unick Fix A Form & Printers	Unick Fix A Form Printers	Unick Fix-A-Form & Printers	Unick Fix-A-Form Printers	Unick Fix A
10	CG-VAK Software & Exports Ltd.	6	CG VAK Software & Exports Ltd	CG VAK Software & Exports	CG Vak Software & Exports	CG-VAK Software & Exports	CG-Vak Software & Exports	CG VAK Software
11
12
13	Deco-Mica Ltd	4	Deco Mica Ltd
14	Deco-Mica Ltd	4	Deco-Mica Ltd
15	Deco-Mica Ltd	4	Deco Mica
16	Deco-Mica Ltd	4	Deco-Mica
17	Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing Industries Ltd
18	Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing
19	Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles &
20	Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles
21	Sainik Finance & Industries Limited	4	Sainik Finance & Industries Limited
22	Sainik Finance & Industries Limited	4	Sainik Finance & Industries
23	Sainik Finance & Industries Limited	4	Sainik Finance & Industri
24	Sainik Finance & Industries Limited	4	Sainik Finance &
25	G-Tech Info-Training Limited	5	G Tech Info Training Limited
26	G-Tech Info-Training Limited	5	G Tech Info Training
27	G-Tech Info-Training Limited	5	G-Tech Info-Training Limited
28	G-Tech Info-Training Limited	5	G Tech Info
29	G-Tech Info-Training Limited	5	G-Tech Info-Training
30	Kome-on Communication Ltd.	5	Kome on Communication Ltd
31	Kome-on Communication Ltd.	5	Kome-on Communication Ltd
32	Kome-on Communication Ltd.	5	Kome On Communication
33	Kome-on Communication Ltd.	5	Kome on Communication
34	Kome-on Communication Ltd.	5	Kome-On Communication
35	Associated Alcohol and Breweries Ltd	5	Associated Alcohol & Breweries Ltd
36	Associated Alcohol and Breweries Ltd	5	Associated Alcohols & Breweries
37	Associated Alcohol and Breweries Ltd	5	Associated Alcohol & Breweries
38	Associated Alcohol and Breweries Ltd	5	Associated Alcohol &
39	Associated Alcohol and Breweries Ltd	5	Associated Alcohols
40	Indo US Bio-Tech Ltd.	6	Indo US Bio Tech Ltd
41	Indo US Bio-Tech Ltd.	6	Indo US Bio-Tech Ltd
42	Indo US Bio-Tech Ltd.	6	Indo US Bio Tech
43	Indo US Bio-Tech Ltd.	6	Indo US Bio-Tech
44	Indo US Bio-Tech Ltd.	6	Indo US BioTech
45	Indo US Bio-Tech Ltd.	6	Indo US Bio-Tech
46	Unick Fix-A-Form & Printers Ltd	6	Unick Fix A Form & Printers Ltd
47	Unick Fix-A-Form & Printers Ltd	6	Unick Fix A Form & Printers
48	Unick Fix-A-Form & Printers Ltd	6	Unick Fix A Form Printers
49	Unick Fix-A-Form & Printers Ltd	6	Unick Fix-A-Form & Printers
50	Unick Fix-A-Form & Printers Ltd	6	Unick Fix-A-Form Printers
51	Unick Fix-A-Form & Printers Ltd	6	Unick Fix A
52	CG-VAK Software & Exports Ltd.	6	CG VAK Software & Exports Ltd
53	CG-VAK Software & Exports Ltd.	6	CG VAK Software & Exports
54	CG-VAK Software & Exports Ltd.	6	CG Vak Software & Exports
55	CG-VAK Software & Exports Ltd.	6	CG-VAK Software & Exports
56	CG-VAK Software & Exports Ltd.	6	CG-Vak Software & Exports
57	CG-VAK Software & Exports Ltd.	6	CG VAK Software
Sheet1

 

[Suman Mehta]

Company Name	Count of Names	1	2	3
Deco-Mica Ltd	4	Deco Mica Ltd
Deco-Mica Ltd	4	Deco-Mica Ltd
Deco-Mica Ltd	4	Deco Mica
Deco-Mica Ltd	4	Deco-Mica
Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing Industries Ltd
Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing
Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles &
Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles
Sainik Finance & Industries Limited	4	Sainik Finance & Industries Limited
Sainik Finance & Industries Limited	4	Sainik Finance & Industries
Sainik Finance & Industries Limited	4	Sainik Finance & Industri
Sainik Finance & Industries Limited	4	Sainik Finance &

 

[Suman Mehta]

Thankyou for the Suggestion, Peter.
This above is the expected result. Still not able to automate it. I have 40K+ rows of this type

 

[CA_Punit]

This can be easily done through Power Query.

Just See a Youtube video as how to do Unpivot data in Power Query.

 

[Suman Mehta]

This can be easily done through Power Query.

Just See a Youtube video as how to do Unpivot data in Power Query.

Thank you, Punit. This worked.
But there is one problem as shown in the image( last column). it is only unpivoting 2 column data. Any idea why is this happening & how to fix this?

 

[CA_Punit]

It is working in my case. You to select all the Columns which you want to unpivot

My Example

Book1
	A	B	C	D
1	Company Name	Row No	Attribute	Value
2	Deco-Mica Ltd	1	1	Deco Mica Ltd
3	Deco-Mica Ltd	1	2	Deco-Mica Ltd
4	Deco-Mica Ltd	1	3	Deco Mica
5	Deco-Mica Ltd	1	4	Deco-Mica
6	Bharat Textiles & Proofing Industries Ltd.	2	1	Bharat Textiles & Proofing Industries Ltd
7	Bharat Textiles & Proofing Industries Ltd.	2	2	Bharat Textiles & Proofing
8	Bharat Textiles & Proofing Industries Ltd.	2	3	Bharat Textiles &
9	Bharat Textiles & Proofing Industries Ltd.	2	4	Bharat Textiles
10	Sainik Finance & Industries Limited	3	1	Sainik Finance & Industries Limited
11	Sainik Finance & Industries Limited	3	2	Sainik Finance & Industries
12	Sainik Finance & Industries Limited	3	3	Sainik Finance & Industri
13	Sainik Finance & Industries Limited	3	4	Sainik Finance &
14	G-Tech Info-Training Limited	4	1	G Tech Info Training Limited
15	G-Tech Info-Training Limited	4	2	G Tech Info Training
16	G-Tech Info-Training Limited	4	3	G-Tech Info-Training Limited
17	G-Tech Info-Training Limited	4	4	G Tech Info
18	G-Tech Info-Training Limited	4	5	G-Tech Info-Training
19	Kome-on Communication Ltd.	5	1	Kome on Communication Ltd
20	Kome-on Communication Ltd.	5	2	Kome-on Communication Ltd
21	Kome-on Communication Ltd.	5	3	Kome On Communication
22	Kome-on Communication Ltd.	5	4	Kome on Communication
23	Kome-on Communication Ltd.	5	5	Kome-On Communication
24	Associated Alcohol and Breweries Ltd	6	1	Associated Alcohol & Breweries Ltd
25	Associated Alcohol and Breweries Ltd	6	2	Associated Alcohols & Breweries
26	Associated Alcohol and Breweries Ltd	6	3	Associated Alcohol & Breweries
27	Associated Alcohol and Breweries Ltd	6	4	Associated Alcohol &
28	Associated Alcohol and Breweries Ltd	6	5	Associated Alcohols
29	Indo US Bio-Tech Ltd.	6	1	Indo US Bio Tech Ltd
30	Indo US Bio-Tech Ltd.	6	2	Indo US Bio-Tech Ltd
31	Indo US Bio-Tech Ltd.	6	3	Indo US Bio Tech
32	Indo US Bio-Tech Ltd.	6	4	Indo US Bio-Tech
33	Indo US Bio-Tech Ltd.	6	5	Indo US BioTech
34	Indo US Bio-Tech Ltd.	6	6	Indo US Bio-Tech
35	Unick Fix-A-Form & Printers Ltd	7	1	Unick Fix A Form & Printers Ltd
36	Unick Fix-A-Form & Printers Ltd	7	2	Unick Fix A Form & Printers
37	Unick Fix-A-Form & Printers Ltd	7	3	Unick Fix A Form Printers
38	Unick Fix-A-Form & Printers Ltd	7	4	Unick Fix-A-Form & Printers
39	Unick Fix-A-Form & Printers Ltd	7	5	Unick Fix-A-Form Printers
40	Unick Fix-A-Form & Printers Ltd	7	6	Unick Fix A
41	CG-VAK Software & Exports Ltd.	8	1	CG VAK Software & Exports Ltd
42	CG-VAK Software & Exports Ltd.	8	2	CG VAK Software & Exports
43	CG-VAK Software & Exports Ltd.	8	3	CG Vak Software & Exports
44	CG-VAK Software & Exports Ltd.	8	4	CG-VAK Software & Exports
45	CG-VAK Software & Exports Ltd.	8	5	CG-Vak Software & Exports
46	CG-VAK Software & Exports Ltd.	8	6	CG VAK Software
Sheet4

 

[CA_Punit]

Also try this formula based Answer with two helper column market in Yellow

This is a trial if you like it we can modify (As the formula wont work if you delete or add rows and column

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Company Name	-	Helper	Row No	1	2	3	4	5	6		Company Name	Count	Name
2	Deco-Mica Ltd	4	-	1.00	Deco Mica Ltd	Deco-Mica Ltd	Deco Mica	Deco-Mica				Deco-Mica Ltd	4	Deco Mica Ltd
3	Bharat Textiles & Proofing Industries Ltd.	4	4.00	2.00	Bharat Textiles & Proofing Industries Ltd	Bharat Textiles & Proofing	Bharat Textiles &	Bharat Textiles				Deco-Mica Ltd	4	Deco-Mica Ltd
4	Sainik Finance & Industries Limited	4	8.00	3.00	Sainik Finance & Industries Limited	Sainik Finance & Industries	Sainik Finance & Industri	Sainik Finance &				Deco-Mica Ltd	4	Deco Mica
5	G-Tech Info-Training Limited	5	12.00	4.00	G Tech Info Training Limited	G Tech Info Training	G-Tech Info-Training Limited	G Tech Info	G-Tech Info-Training			Deco-Mica Ltd	4	Deco-Mica
6	Kome-on Communication Ltd.	5	17.00	5.00	Kome on Communication Ltd	Kome-on Communication Ltd	Kome On Communication	Kome on Communication	Kome-On Communication			Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing Industries Ltd
7	Associated Alcohol and Breweries Ltd	5	22.00	6.00	Associated Alcohol & Breweries Ltd	Associated Alcohols & Breweries	Associated Alcohol & Breweries	Associated Alcohol &	Associated Alcohols			Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing
8	Indo US Bio-Tech Ltd.	6	27.00	7.00	Indo US Bio Tech Ltd	Indo US Bio-Tech Ltd	Indo US Bio Tech	Indo US Bio-Tech	Indo US BioTech	Indo US Bio-Tech		Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles &
9	Unick Fix-A-Form & Printers Ltd	5	33.00	8.00	Unick Fix A Form & Printers Ltd	Unick Fix A Form & Printers	Unick Fix A Form Printers	Unick Fix-A-Form & Printers	Unick Fix-A-Form Printers	Unick Fix A		Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles
10	CG-VAK Software & Exports Ltd.	6	38.00	9.00	CG VAK Software & Exports Ltd	CG VAK Software & Exports	CG Vak Software & Exports	CG-VAK Software & Exports	CG-Vak Software & Exports	CG VAK Software		Sainik Finance & Industries Limited	4	Sainik Finance & Industries Limited
11												Sainik Finance & Industries Limited	4	Sainik Finance & Industries
12												Sainik Finance & Industries Limited	4	Sainik Finance & Industri
13												Sainik Finance & Industries Limited	4	Sainik Finance &
14												G-Tech Info-Training Limited	5	G Tech Info Training Limited
15												G-Tech Info-Training Limited	5	G Tech Info Training
16												G-Tech Info-Training Limited	5	G-Tech Info-Training Limited
17												G-Tech Info-Training Limited	5	G Tech Info
18												G-Tech Info-Training Limited	5	G-Tech Info-Training
19												Kome-on Communication Ltd.	5	Kome on Communication Ltd
20												Kome-on Communication Ltd.	5	Kome-on Communication Ltd
21												Kome-on Communication Ltd.	5	Kome On Communication
22												Kome-on Communication Ltd.	5	Kome on Communication
23												Kome-on Communication Ltd.	5	Kome-On Communication
24												Associated Alcohol and Breweries Ltd	5	Associated Alcohol & Breweries Ltd
25												Associated Alcohol and Breweries Ltd	5	Associated Alcohols & Breweries
26												Associated Alcohol and Breweries Ltd	5	Associated Alcohol & Breweries
27												Associated Alcohol and Breweries Ltd	5	Associated Alcohol &
28												Associated Alcohol and Breweries Ltd	5	Associated Alcohols
29												Indo US Bio-Tech Ltd.	6	Indo US Bio Tech Ltd
30												Indo US Bio-Tech Ltd.	6	Indo US Bio-Tech Ltd
31												Indo US Bio-Tech Ltd.	6	Indo US Bio Tech
32												Indo US Bio-Tech Ltd.	6	Indo US Bio-Tech
33												Indo US Bio-Tech Ltd.	6	Indo US BioTech
34												Indo US Bio-Tech Ltd.	6	Indo US Bio-Tech
35												Unick Fix-A-Form & Printers Ltd	5	Unick Fix A Form & Printers Ltd
36												Unick Fix-A-Form & Printers Ltd	5	Unick Fix A Form & Printers
37												Unick Fix-A-Form & Printers Ltd	5	Unick Fix A Form Printers
38												Unick Fix-A-Form & Printers Ltd	5	Unick Fix-A-Form & Printers
39												Unick Fix-A-Form & Printers Ltd	5	Unick Fix-A-Form Printers
40												CG-VAK Software & Exports Ltd.	6	CG VAK Software & Exports Ltd
41												CG-VAK Software & Exports Ltd.	6	CG VAK Software & Exports
42												CG-VAK Software & Exports Ltd.	6	CG Vak Software & Exports
43												CG-VAK Software & Exports Ltd.	6	CG-VAK Software & Exports
44												CG-VAK Software & Exports Ltd.	6	CG-Vak Software & Exports
45												CG-VAK Software & Exports Ltd.	6	CG VAK Software
46
47
48
49
Sheet1

Cell Formulas
Range	Formula
B1	B1	=0
L2:L49	L2	=IF(ROWS($L$2:L2)>SUM($B$2:$B$10),"",VLOOKUP(LOOKUP(ROWS($1:1)-1,$C$2:$C$10,$D$2:$D$10),IF({1,0},$D$2:$D$10,$A$2:$A$10),2,0))
M2:M49	M2	=IF(ROWS($L$2:L2)>SUM($B$2:$B$10),"",VLOOKUP(LOOKUP(ROWS($1:1)-1,$C$2:$C$10,$D$2:$D$10),IF({1,0},$D$2:$D$10,$B$2:$B$10),2,0))
N2:N49	N2	=IF(ROWS($L$2:L2)>SUM($B$2:$B$10),"",INDIRECT(TEXT(((LOOKUP(ROWS($1:1)-ROWS($1:$1),$C$2:$C$10,$D$2:$D$10)*100)+100)+4+SUMPRODUCT(--((LOOKUP(ROW($1:1)-ROWS($1:$1),$C$2:$C$10,$D$2:$D$10))=LOOKUP(ROWS($1:1)-ROWS($1:$1),$C$2:$C$10,$D$2:$D$10))),"R00C00"),FALSE))
C3:C10	C3	=C2+B3-(B3-B2)

 

[Peter_SSs]

This above is the expected result.

That appears to be identical to the first 12 rows of the results produced by my code (rows 13 to 24 in my screen shot). What were the first 12 rows of the results when you ran my code on the sample data from post 1?
Or else can you explain in what way my code failed for you?

 

[Suman Mehta]

Also try this formula based Answer with two helper column market in Yellow

This is a trial if you like it we can modify (As the formula wont work if you delete or add rows and column

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Company Name	-	Helper	Row No	1	2	3	4	5	6		Company Name	Count	Name
2	Deco-Mica Ltd	4	-	1.00	Deco Mica Ltd	Deco-Mica Ltd	Deco Mica	Deco-Mica				Deco-Mica Ltd	4	Deco Mica Ltd
3	Bharat Textiles & Proofing Industries Ltd.	4	4.00	2.00	Bharat Textiles & Proofing Industries Ltd	Bharat Textiles & Proofing	Bharat Textiles &	Bharat Textiles				Deco-Mica Ltd	4	Deco-Mica Ltd
4	Sainik Finance & Industries Limited	4	8.00	3.00	Sainik Finance & Industries Limited	Sainik Finance & Industries	Sainik Finance & Industri	Sainik Finance &				Deco-Mica Ltd	4	Deco Mica
5	G-Tech Info-Training Limited	5	12.00	4.00	G Tech Info Training Limited	G Tech Info Training	G-Tech Info-Training Limited	G Tech Info	G-Tech Info-Training			Deco-Mica Ltd	4	Deco-Mica
6	Kome-on Communication Ltd.	5	17.00	5.00	Kome on Communication Ltd	Kome-on Communication Ltd	Kome On Communication	Kome on Communication	Kome-On Communication			Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing Industries Ltd
7	Associated Alcohol and Breweries Ltd	5	22.00	6.00	Associated Alcohol & Breweries Ltd	Associated Alcohols & Breweries	Associated Alcohol & Breweries	Associated Alcohol &	Associated Alcohols			Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles & Proofing
8	Indo US Bio-Tech Ltd.	6	27.00	7.00	Indo US Bio Tech Ltd	Indo US Bio-Tech Ltd	Indo US Bio Tech	Indo US Bio-Tech	Indo US BioTech	Indo US Bio-Tech		Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles &
9	Unick Fix-A-Form & Printers Ltd	5	33.00	8.00	Unick Fix A Form & Printers Ltd	Unick Fix A Form & Printers	Unick Fix A Form Printers	Unick Fix-A-Form & Printers	Unick Fix-A-Form Printers	Unick Fix A		Bharat Textiles & Proofing Industries Ltd.	4	Bharat Textiles
10	CG-VAK Software & Exports Ltd.	6	38.00	9.00	CG VAK Software & Exports Ltd	CG VAK Software & Exports	CG Vak Software & Exports	CG-VAK Software & Exports	CG-Vak Software & Exports	CG VAK Software		Sainik Finance & Industries Limited	4	Sainik Finance & Industries Limited
11												Sainik Finance & Industries Limited	4	Sainik Finance & Industries
12												Sainik Finance & Industries Limited	4	Sainik Finance & Industri
13												Sainik Finance & Industries Limited	4	Sainik Finance &
14												G-Tech Info-Training Limited	5	G Tech Info Training Limited
15												G-Tech Info-Training Limited	5	G Tech Info Training
16												G-Tech Info-Training Limited	5	G-Tech Info-Training Limited
17												G-Tech Info-Training Limited	5	G Tech Info
18												G-Tech Info-Training Limited	5	G-Tech Info-Training
19												Kome-on Communication Ltd.	5	Kome on Communication Ltd
20												Kome-on Communication Ltd.	5	Kome-on Communication Ltd
21												Kome-on Communication Ltd.	5	Kome On Communication
22												Kome-on Communication Ltd.	5	Kome on Communication
23												Kome-on Communication Ltd.	5	Kome-On Communication
24												Associated Alcohol and Breweries Ltd	5	Associated Alcohol & Breweries Ltd
25												Associated Alcohol and Breweries Ltd	5	Associated Alcohols & Breweries
26												Associated Alcohol and Breweries Ltd	5	Associated Alcohol & Breweries
27												Associated Alcohol and Breweries Ltd	5	Associated Alcohol &
28												Associated Alcohol and Breweries Ltd	5	Associated Alcohols
29												Indo US Bio-Tech Ltd.	6	Indo US Bio Tech Ltd
30												Indo US Bio-Tech Ltd.	6	Indo US Bio-Tech Ltd
31												Indo US Bio-Tech Ltd.	6	Indo US Bio Tech
32												Indo US Bio-Tech Ltd.	6	Indo US Bio-Tech
33												Indo US Bio-Tech Ltd.	6	Indo US BioTech
34												Indo US Bio-Tech Ltd.	6	Indo US Bio-Tech
35												Unick Fix-A-Form & Printers Ltd	5	Unick Fix A Form & Printers Ltd
36												Unick Fix-A-Form & Printers Ltd	5	Unick Fix A Form & Printers
37												Unick Fix-A-Form & Printers Ltd	5	Unick Fix A Form Printers
38												Unick Fix-A-Form & Printers Ltd	5	Unick Fix-A-Form & Printers
39												Unick Fix-A-Form & Printers Ltd	5	Unick Fix-A-Form Printers
40												CG-VAK Software & Exports Ltd.	6	CG VAK Software & Exports Ltd
41												CG-VAK Software & Exports Ltd.	6	CG VAK Software & Exports
42												CG-VAK Software & Exports Ltd.	6	CG Vak Software & Exports
43												CG-VAK Software & Exports Ltd.	6	CG-VAK Software & Exports
44												CG-VAK Software & Exports Ltd.	6	CG-Vak Software & Exports
45												CG-VAK Software & Exports Ltd.	6	CG VAK Software
46
47
48
49
Sheet1

Cell Formulas
Range	Formula
B1	B1	=0
L2:L49	L2	=IF(ROWS($L$2:L2)>SUM($B$2:$B$10),"",VLOOKUP(LOOKUP(ROWS($1:1)-1,$C$2:$C$10,$D$2:$D$10),IF({1,0},$D$2:$D$10,$A$2:$A$10),2,0))
M2:M49	M2	=IF(ROWS($L$2:L2)>SUM($B$2:$B$10),"",VLOOKUP(LOOKUP(ROWS($1:1)-1,$C$2:$C$10,$D$2:$D$10),IF({1,0},$D$2:$D$10,$B$2:$B$10),2,0))
N2:N49	N2	=IF(ROWS($L$2:L2)>SUM($B$2:$B$10),"",INDIRECT(TEXT(((LOOKUP(ROWS($1:1)-ROWS($1:$1),$C$2:$C$10,$D$2:$D$10)*100)+100)+4+SUMPRODUCT(--((LOOKUP(ROW($1:1)-ROWS($1:$1),$C$2:$C$10,$D$2:$D$10))=LOOKUP(ROWS($1:1)-ROWS($1:$1),$C$2:$C$10,$D$2:$D$10))),"R00C00"),FALSE))
C3:C10	C3	=C2+B3-(B3-B2)

Thanks a lot. I sorted the data in desc order and then it worked perfectly using power query. Will try this formula also.