Transposing Columns to Row based on Cell Contents

**Firestone** · 07-09-2015, 02:54 PM

Hi Everyone,

I'm trying to import data from Excel into ArcMap and I'm running across some issues with a database. Basically I need each building (row) to contain links to all drawing data. Different buildings may have different numbers of drawings.

Basically I want to convert something that looks like this:

Capture.JPG

Into something that looks like this:

Capture2.JPG

The database is quite large so doing this manually is pretty much out. I do know the max number of columns I'd need per building by using a "COUNTIF" function so I know how many rows to title.

Any help would be great. Thank you

**Bernie Deitrick** · 07-09-2015, 04:18 PM

First, make your headings in row 1.

Then, in C2, enter the formula

=IF($A2=OFFSET($A2,COLUMN(A1),0),OFFSET($B2,COLUMN(A1),0),"")

and copy to the right for as many columns as you need, then copy down to match your list.

Copy all your cells, paste values, then select all the cells and use Remove Duplicates, based on column A only. And you're done.

**newdoverman** · 07-09-2015, 04:50 PM

Personally, I wouldn't do what you are asking. It is adding a complication to your data. All the data for a building can be extracted by filtering and /or sorting to get the data together. This keeps the size of the workbook to a minimum and there are no complications of not preparing for the amount of data that you may have. You may have prepared for 15 records when you actually have 20 or more with the list growing.

If you really want to go horizontal, here is a solution that you can expand horizontally as required.

Enter the column Headings in D1:N1 (to start) D1 will be Building and under that the buildings will be entered using this formula starting in D2 to extract a unique listing of all the buildings in column A.

Formula:

Please Login or Register  to view this content.

This is an ARRAY FORMULA so enter with Ctrl + Shift + Enter

In E2 enter this formula and fill across and down:

Formula:

Please Login or Register  to view this content.

This is also an ARRAY FORMULA so enter with Ctrl + Shift + Enter

The result will be:

	D	E	F	G	H	I	J	K	L	M	N
1	Building	Drawing 1	Drawing 2	Drawing 3	Drawing 4	Drawing 5	Drawing 6	Drawing 7	Drawing 8	Drawing 9	Drawing 10
2	A	1	2	3	3	3	4	5
3	B	6	7	8	8	8	9
4	C	10	11
5	D	12
6	E	13	14	15	15	15
7