asked on

Copy Excel Column Letters into First Row

Importing data into programs via Excel sheets. Sheets contain many columns, need to create "specs" in other programs referencing the row letters above column headings in order to match data with import. How can I easily get the letters (or numbers) above my column headers to fill down into the first row of my sheet? That way I could copy the two top rows that contain my headers and the letters above, and then paste and transpose, so I can use as a reference guide when completing the match up during the import process.

HainKurt

please create a demo excel and show what you have and what you want to achieve
difficult to follow this way

CynSzcz

ASKER

Attached is a PDF of what I do.
Excel-Column-Header-Key.pdf

CynSzcz

ASKER

My question HainKurt, is whether or not there is way to get those letters into the first row of my sheet which I have inserted above my columns, without having to type them in each individually. Is there a way to somehow use the "fill" feature to place them?

thanks

HainKurt

type 65 in A1, copy left with Ctrl so it becomes 65,66,67,...
then under 65, A2 type "=CHAR(A1)", copy left
so it becomes A.B,C,...

you can hide row 1 or leave as is, or copy row 2 and paste special / values into row 3 and delete first 2 rows

I still could not get what you do here :)

CynSzcz

ASKER

I don't mean what you are saying about "copy left." I just want to put the letters above the columns (A thru AC . . .) in the first column of my sheet.

CynSzcz

ASKER

I tried filling in the row with a series but when it gets to Z, it starts with A again, instead of AA, AB, AC, AD

HainKurt

just try this in any cell

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

or use

=SUBSTITUTE(ADDRESS(1,COLUMN()-3,4),"1","")

if you start from C (substruct 3)

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CynSzcz

ASKER

YES !!!! That is exactly what I want. But I am afraid that I have absolutely no idea what to do with that suggestion that you posted.

Norie

Which post are you referring to?

CynSzcz

ASKER

When you said, "something like this may help." I am afraid I do not understand all that code stuff.

Norie

Well the code I posted would generate a list of the headers, with their corresponding column letters, for the active sheet and put that list on a new sheet.

To use is follow these steps.

1 Open the Visual Basic Editor (ALT+F11).

2 Goto Insert>Module.

3 Copy the code I posted and paste it into the module created in step 2.

4 Return to Excel, close the Visual Basic Editor if you want.

5 Select the sheet you want to create the header/column letter list for.

6 Open the Macro Dialog (ALT+F8).

7 Choose CreateHeaderMapping from the list of macros and click Run.

If you don't want to use code this could done using formulas.

First copy the headers and paste and transpose them somewhere.

Then put this formula in the cell next to the cell that has first header in the range you pasted to and copy it down.

=SUBSTITUTE(ADDRESS(1,ROWS($A$6:A6),4),"1","")

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CynSzcz

ASKER

Thanks Rob, that does work!!! Also someone gave me the formula below which works too. Thank you for providing your expertise to others. This will make my job much easier.

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")