Read column B and turn it into header rows


New Member
Jul 24, 2014
I have a sheet similar to the following. Each section changes in length daily, so I need to be able to have my macro read this dynamically.

Name Rank
John Smith First
Angela Simms First
Mike Dunn First
Paul Nabors First
Tim Evans Second
Frank White Second
Gina Crow Second
Laura Wild Third
George Amos Third
Amy Jackson Third
James Shelton Third

I have up to 12 rankings, and each one can contain any number of entries daily. I'd like the macro to output something like:

John Smith
Angela Simms
Mike Dunn
Paul Nabors
Tim Evans
Frank White
Gina Crow
Laura Wild
George Amos
Amy Jackson
James Shelton

I should note that there are other columns of data to be transferred with each person. I can move the Rank column to column A if need be.

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Yep, just move rank to column A and sort by that column. Does that give you what you want?
Upvote 0
It will already be sorted by rank before I run the macro. I need the macro to turn the "rank" into a header row for all names that apply to that rank.
Upvote 0
I always try to think of ways to do things without using macros / VBA code, but if you really must have rank on a separate row, then you might have to use VBA... but maybe check if The "Subtotals" function does what you want, or using a pivot table.
Upvote 0
It's probably fastest to just manually insert the headers where you need them, for this few # of ranks.
Upvote 0
I'll be exporting this data into excel multiple times daily, trying to automate the process rather than spending the time inserting headers each time I export.
Upvote 0
does it have to be in one column? or would this be acceptable?
John SmithTim EvansLaura Wild
Angela SimmsFrank WhiteGeorge Amos
Mike DunnGina CrowAmy Jackson
Paul NaborsJames Shelton

Upvote 0
The "rank" for each group needs to move to its own row, then the names (and subsequent data contained in other columns) will fill the rows below, until the macro realizes a different "rank", creates a new header and continues
Upvote 0
Did you start to learn about VBA yet? If so, post what VBA code you have so far. If not, do web searches for things like :

"Excel VBA getting started"
"Excel VBA loop through cells"
"Excel VBA read cell value"
"Excel VBA insert row"
"Excel VBA set cell value"
"Excel VBA format cell" (if you need it in bold like that)

(trying to empower you to learn this stuff) :)

You could also use the Record Macro button on the Developer tab to see what code it generates for you when you do certain things. (Web search "Excel Record Macro" to learn more)

Okay, I decided I'll be nice to help you get started- from Excel, press Alt-F11 to open your code editor, click Insert --> Module, then type :

function myFunction

and press enter. Then you can type or paste code into that function, and press F5 to run it.
Last edited:
Upvote 0
Oh- when you say you'll be exporting it to Excel- does it always create a new workbook when you do that? Or can you copy and paste it into an existing workbook?
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back