0

given N number of columns with a header and a list underneath I would like to convert this to a database format with the header in the first column and the items of the list in the second.

Column1    Column2    Column3    Column4    Column5
Data1      Data1      Data3
Data2                 Data4

So this data then becomes

Column1    Data1
Column1    Data2
Column2    Data1
Column3    Data3
Column3    Data4

I would like do do this with excel formulas so that as I add data columns this information will automatically update without having to run macros or pivot tables.

This will be two formulas, one for each column and can just be dragged down. The answer will most likely be some form of index/match/countif array monstrosity.

Not opposed to adding helper columns. This page has me halfway there: http://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html#a1

0

1 Answer 1

0

So the link I found cascaded into a solution but it took more than 2 formulas.

http://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html#a1

And here is a link to an example sheet I created: https://drive.google.com/file/d/0B4hGhOqw8ilUOVdkMGVLLVR2Uzg/view?usp=sharing

This covers the Header migration, but in order to get to this point requires 2 more columns of data that need formulas as well.

for people who's header is static you need a way to get the headers into a column, my headers were already a transpose from another column so I had that done, but something like:

=OFFSET(FirstHeader,0,ROW()-CurrentRowNumber)&""

could get your column headers into a column for you. So now you have:

ColumnHeaders
Column1
Column2
Column3

from:

Column1    Column2    Column3    BlankColumn    BlankColumn
Data1      Data1      Data3
Data2                 Data4

Now I needed a count of the number of data items in each column. Which I got from this LittleMonstrosity:

=IF(B2<>"",SUMPRODUCT(--(INDIRECT(ADDRESS(FirstRowIndex,MATCH(B2,$A$1:$AA$1,0))&":"&ADDRESS(LastRowIndex,MATCH(B2,$A$1:$AA$1,0)))<>"")),"")

Where the First and Last row index are a predefined bound for how large of lists I'm going to accept because of memory limits.Similar to the reasoning for only allowing A to AA for columns. Maybe I'll move my bounding box out later so I can change it from one location.

So using this I now get:

CountIndices   ColumnHeaders
2              Column1
1              Column2
2              Column3

but I need actual start indices not just counts so I put 1 in the first row of the start indices and wrapped the little monstrosity in:

=IFERROR(A2+LittleMonstrosity,"")

to get:

StartIndices   ColumnHeaders
1              Column1
3              Column2
4              Column3
6

Only now can I use the example code from the link I found to derive my first data column:

=VLOOKUP(ROWS($B$2:$B2),DataRange,2)

Once again with those bounds built into the DataRange. Soon I'll even make that dynamic within reason.

Now I have my first Header column:

A              B                M         N
StartIndices   ColumnHeaders    Header    Data
1              Column1          Column1
3              Column2          Column1
4              Column3          Column2
6                               Column3
                                Column3

Now to fetch the data for each column we build a counter that counts the number of times the paired header appears in the column above it and uses that to index into the data after finding the correct column using this formula, keeping in mind that in this example The header is in the M column:

=IF(M2<>"",INDEX(DataRange,COUNTIF($M$2:$M2,M2),MATCH(M2,HeaderRange,0)),"")

Now I finally get:

A              B                M         N
StartIndices   ColumnHeaders    Header    Data
1              Column1          Column1   Data1
3              Column2          Column1   Data2
4              Column3          Column2   Data1
6                               Column3   Data3
                                Column3   Data4

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .