Read column B and turn it into header rows

jmmac

New Member
Joined
Jul 24, 2014
Messages
30
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:

First
John Smith
Angela Simms
Mike Dunn
Paul Nabors
Second
Tim Evans
Frank White
Gina Crow
Third
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
Yep, just move rank to column A and sort by that column. Does that give you what you want?
 

jmmac

New Member
Joined
Jul 24, 2014
Messages
30
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.
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
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.
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221

ADVERTISEMENT

It's probably fastest to just manually insert the headers where you need them, for this few # of ranks.
 

jmmac

New Member
Joined
Jul 24, 2014
Messages
30
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.
 

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

does it have to be in one column? or would this be acceptable?
FirstSecondThird
John SmithTim EvansLaura Wild
Angela SimmsFrank WhiteGeorge Amos
Mike DunnGina CrowAmy Jackson
Paul NaborsJames Shelton

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

jmmac

New Member
Joined
Jul 24, 2014
Messages
30
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
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
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:

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,646
Members
414,398
Latest member
dhune

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 MrExcel.com.
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 "mrexcel.com".
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
Top