Combining Data multiple tabs
 

I have a spreadsheet that has multiple tabs with the same data on each
tab. The data is input line after line. Example appears below

Name : John Smith
Address : 123 Main Street
City: Anytown
State: Ca.
Zip: 12345

I need to gather the data from each tab and put it into a columnar
format
that looks like this

Name Address City State
Zip
John Smith 123 Main Street Anytown Ca 12345

Basically the data ends up fitting nicely into columns instead of
rows.

I can then make a relational database out of the data. I have tried
the
=(cell#) and have tried to make the cells absolute on the reference
but
it does not work.

Any help would be appreciated.

Thanks,

Damion


--
zero760
------------------------------------------------------------------------
zero760's Profile: http://www.excelforum.com/member.php...o&userid=26800
View this thread: http://www.excelforum.com/showthread...hreadid=400545

Here's one way. This solution assumes your data is in column A and
each entry appears with that colon in it, per your example; it assumes
a single blank row appears in between entries; it requires a single
blank row before the first entry. In the mockup I created data starts
in A2.

On a new tab the formula in cell A2 is
=IF(Sheet1!A1="",MID(Sheet1!A2,FIND(":",Sheet1!A2, 1)+2,LEN(Sheet1!A2)),"")

In B2 the formula is
=IF(Sheet1!A1="",MID(Sheet1!A3,FIND(":",Sheet1!A3, 1)+2,LEN(Sheet1!A3)),"")

In C2 the formula is
=IF(Sheet1!A1="",MID(Sheet1!A4,FIND(":",Sheet1!A4, 1)+2,LEN(Sheet1!A4)),"")

In D2 the formula is
=IF(Sheet1!A1="",MID(Sheet1!A5,FIND(":",Sheet1!A5, 1)+2,LEN(Sheet1!A5)),"")

In E2 the formula is
=IF(Sheet1!A1="",MID(Sheet1!A6,FIND(":",Sheet1!A6, 1)+2,LEN(Sheet1!A6)),"")

Copy row 2 and paste it into new rows until all entries from the source
sprdsht are captured. Blank rows will appear in between entries: you
can sort these out of the data set by highlighting rows and sorting on
column A. At some point you may need to copy the data and paste it
over itself as values.

Dave O

Thanks for your response. Here is how the data is input.

Name of property: XYZ (location cell A4)
Property address: 123 Main Street (location cell A5)
City: Anytown (location cell A6)
Cross Street: First Street (location cell a7)

There are no lines that seperate the data. I need to just extract out
the information after the :

The "Name of property:" would become a header on the excel spreadsheet
in column A. The "Property address" would become a header on the excel
spreadsheet in column B - etc. etc. etc.

I could then sort the database after conversion out of the row format
into the columnar format. For example by city or street etc.

I would input the header info into the spreadsheet so I do not really
need to save this as I could do a find and replace it and just get rid
of it that way.

Let me know if this makes sense.

Thanks for all your help.

Damion


--
zero760
------------------------------------------------------------------------
zero760's Profile: http://www.excelforum.com/member.php...o&userid=26800
View this thread: http://www.excelforum.com/showthread...hreadid=400545