I want to add a column named serial number and fill in that column till n where n is number of count of items in the adjacent column.

[sachin772020]

Hi All,

I want to add a column named serial number and fill in that column till n where n is number of count of items in the adjacent column. Please suggest a VBA code for this.


PFB, I have shown the raw data which we get from the tool and the required format.

The number of rows will be dynamic everyday. I have also attached the screenshot for the same.

Raw Data				Required Data
Item	Rank			Sr. No.	Item	Rank	Rank	Rank
A	210 450 (-1%)			1	A	210	450	(-1%)
B	20 450 (-1%)			2	B	20	450	(-1%)
C	230 500 (1%)			3	C	230	500	-1%
D	960 480 (-9%)			4	D	960	480	(-9%)

Regards, Sachin

 

[alansidman]

Book5
	A	B	C	D	E
1	Index	Item	Rank.1	Rank.2	Rank.3
2	1	A	210	450	(-1%)
3	2	B	20	450	(-1%)
4	3	C	230	500	(1%)
5	4	D	960	480	(-9%)
Sheet2

Here is the Mcode from Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Rank", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Rank", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Rank.1", "Rank.2", "Rank.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Rank.1", Int64.Type}, {"Rank.2", Int64.Type}, {"Rank.3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Item", "Rank.1", "Rank.2", "Rank.3"})
in
    #"Reordered Columns"

 

[sachin772020]

Hi alansidman, The number of rows will be dynamic everyday. I needed a vba macro code for the same. I have shown the raw data and the required output.

 

[alansidman]

Using Power Query, if you update the original data set and click on the refresh button, then the data in the output will automatically update for you.

EDIT. Just noticed that you are using Excel 2007. This solution will not work for you as PQ was not introduced until Excel 2010. My apologies.

 

[Fluff]

Cross posted VBA macro code to add a column named serial number and fill in that column till n where n is number of count of items in the adjacent column.

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.