Inserting header rows based on sorted column

[rap66]

Hi

Please see attachment. I have data in columns A:F. They are sorted by column A (smallest to largest). Is there a way to insert rows (of merged cells A:F) with numbers at the center that act as headers, and that indicate the beginning of the group of the numbers found in column A. For example:

A2:F2 should say 1
A5:F5 should say 2
A11:F11 should say 3
A18:F18 should say 5
Etc?

See sample in attachment. I placed the desired results in columns J:O. Preferably I would not want the formula to recreate the list a second time as in the sample. Rather, it should insert it in columns A:F

Thank you

[TMS]

You could use Outline on the Data tab and insert a Subtotal (untick subtotals below). That would insert the rows you want. I think you'd need VBA to create the format that you want. Should be relatively straightforwatd based on the inserted subtotals.

[rap66]

Thanks. The subtotal idea is helpful to insert the rows.
As you said I’d probably need to get someone to write a VBA code to have it formatted the way I wanted.
Thanks anyway.

[TMS]

VBA

Please Login or Register  to view this content.

[TMS]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[rap66]

Thank you very much for working on this.

I am embarrassed to say that I dont know how to put this code in the spreadsheet, and how to activate it once it is there.

Can I bother you for quick instructions, or can you send me a spreadsheet with 5 columns and with the code in it so that i can just insert the data in it, and let me know how to activate it?

Tyvm

[TMS]

Please see the updated sample workbook.

[rap66]

Hi TMS
It works beautifully. Many thanks.
Three requests please.
1. can you move the buttons to the far right, around columns AD and AE.
2. In case i add other columns (G, H, etc) with data, can the header rows also merge those columns accordingly?
3. Most importantly, once the code generates the header rows with number, i dont really need column A which has the same numbers. But if i delete that column, then the information on the header also disappears. Can column A not be part of the merged cells in header? The merged cells should begin with column B.

sorry for this extra work, and thanks again

[TMS]

Adjusted

Please Login or Register  to view this content.

Please note that the Reset Test Data was/is only intended for use during testing. I would suggest you delete it or, at least, don't use it once you add data.

[rap66]

perfect. Many thanks

[TMS]

You're welcome. Thanks for the rep.