How to loop adding a column with row numbers beside each column in a dynamic range in

[Oraekene]

Good day. Been on this for more than a week and can't find a solution. New to VBA. I have a data sheet with multiple columns of data. I would like to add, beside each column, a column with the row numbers of each column, and to do this through the range till it hits an empty column. Something like below. My data looks like the first pic. I'd like a VBA that loops through that and turns it into the second pic. I currently use Excel 2013. Would be grateful for any response

UPDATE: Apologies for posting pictures. I attached the sample workbook with the example sheets as in the pics. First two sheets are the before and after sheets. Then i added 2 more sheets to show how i'll be adding columns to the data sheet over time, so the vba needs to work for a dynamic range. Again, grateful for the responses so far

UPDATE 24/09/22: this issue is resolved. The codes worked, thank you so much. I have an additional request: summing total for each unique value, in multiple columns. Similar to how to find for eg. sum of total sales for multiple months for a particular product. I have attached a sample sheet to explain. The update sheets are to show how thr input data worksheet would can expand over time, so the code would need to work for a dynamic range. So far working on it i've found i might need a SUMIF INDEX-MATCH array function, but i don't know how to write it. Would be grateful for any help, but the initial request in this thread is solved, grateful

[alansidman]

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Oraekene]

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Thank you for this. I apologise for the pictures. Will attach the files

[mjr veverka]

Why are you quoting the entire post of alansidman immediately preceding your reply to him ?
Unnecessary operation, it is known who you answer and what you are responding to ...
Try:

Please Login or Register  to view this content.

[beyond Excel]

Good day. Been on this for more than a week and can't find a solution. New to VBA. I have a data sheet with multiple columns of data. I would like to add, beside each column, a column with the row numbers of each column, and to do this through the range till it hits an empty column. Something like below. My data looks like the first pic. I'd like a VBA that loops through that and turns it into the second pic. I currently use Excel 2013. Would be grateful for any response

Hello.
"I suspect" that what you are looking for is an auxiliary step and that your main objective is another: Could you comment, then, for what other objective are you requesting this numbering of rows?...

[alansidman]

@Oraekene
Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

[jolivanes]

People love the "Reply With Quote" Button.
Maybe ExcelForum should have a piggy bank and every time when an unnecessary quote is done the culprit has to donate 25 cents to the piggy bank.
Once a year at Christmas time all the monitors and administrators can have a nice get together and a good meal from it.

Along the same lines but different!

Please Login or Register  to view this content.

[Oraekene]

Good day. I'm attached a xls file of the sample data to my original post. Please check the attachments section, below the pictures

[Oraekene]

Thank you for the reply. You are right. Its an auxillary step. I'll list out the process step by step below.

First this sheet is a master sheet, which summarises the ID column from other sheets. So

1. New sheets are added to the workbook
2. The appropriate column, in this case the ID column, column B, in each worksheet is filtered by time of access (in this case first 24 hours), sorted from earliest time of access to latest time of access, and the final IDs in the ID column are copied to the master sheet
3. Rows numbers, used here to signify rank, are placed beside each column
4. A final column of distinct ID numbers is generated
5. The row numbers for each ID are then sumed up beside their values in a new column
6. The ID numbers are then sorted by the row sums: the ID with the least sum are ranked top (i.e earliest on average) and the ID with the largest sum is ranked last (latest)

No 3 is what i hope to achieve with this VBA. I updated my original post with a link to the sample excel sheet. Would be grateful for a response

[Oraekene]

Noted. Thank you

[Oraekene]

My bad. New to forums in general. Thank you for thr reply. Will try this code now and give feedback

[Oraekene]

Thank you for this. Realised the error after i'd posted the quote. Repliea only from now on😊 Will try out the code and give a reponse, thank you!

[jolivanes]

Re your Post #9.
Just let us know if the two suggestions do what you need. If not, let us know what is wrong.

[beyond Excel]

Hello.
I didn't understand the relationship between your detailed explanation in post #9 and your workbook in post #1.

That's why I am attaching a first treatment of the information in the attached workbook: every time you add a new column in the "input" sheet, you can run the macro and new information will be generated in the "result" sheet.

If correct, indicate (on the "results" sheet itself) what you do next with that information.

Please Login or Register  to view this content.

[Oraekene]

I tried your code and it worked! Thank you! Really grateful