convert multiple tables (1 table/record) into a single table (1 record/row)

[j15]

I need to convert some data and am not real good with Excel so I'm hoping for some help. I've got some reports that output the data with each record as an individual table:

[pre]

Col1     Col2

Field 1  Value1a
Field 2	 Value2a
Field 3	 Value3a
Field 4	 Value4a
Field 5	 Value5a1
Value5a2
Field 6	 value6a
Field 7	 value7a
Field 8	 value8a1
value8a2
value8a3

Field 1: Value1b
Field 2	Value2b
Field 3	Value3b
Field 4	Value4b
Field 5	Value5b1
Value5b2
Value5b3
Field 6	Value6b
Field 7	Value7b
Field 8	Value8b1

I need to get the data into a single table where each field is a column (field 1 through field 13) and each record is a row:


Field 1	Field 2	Field 3	Field 4	Field 5			Field 6	Field 7	Field 8
Value1a	Value2a	Value3a	Value4a	Value5a1, Value5a2	value6a	value7a	value8a1, value8a2
Value1b	Value2b	Value3b	Value4b	Value5b1		Value6b	Value7b	Value8b1, Value8b2, Value8b3

[/pre]
Some fields (field 5 & 8 in my examples) might have multiple values that I would like to have comma separated.


I have more than 20 of these reports and each report has over 100 records so I would like to have a solution that I can reuse and just apply to each report. Each report would remain separate, I just want to convert each report so it becomes a single table that is much easier to sort and read.


I'm hoping somebody can help me out here.

 

[NARAYANK991]

Hi ,


A formula-based solution may not be possible , since it involves concatenation of text values ; is a VBA ( macro ) based solution OK with you ?


If so , it would be better if you could upload one workbook ( 1 report ) so that you would not need to make any changes to the macro to make it work with your data.


This forum does not have a provision for either uploading files or attaching files to posts.


So what you need to do is use your preferred public file-sharing website ( RapidShare , Hotfile , DropBox , SkyDrive , GoogleDocs , SpeedyShare ... ) to upload your file , give others permission to access and download the file , and then post that access link here , in this same topic.


Narayan

 

[srinidhi]

The Transpose formula, can help you in converting rows to column & column to rows.

 

[bobhc]

Good day j15


As srinidhi has pointed out if you copy your two columns and then click in the cell where you want the data to start, then go to paste/transpose that will do as you have indicated.


This link will show what I believe you want


https://dl.dropboxusercontent.com/u/75495784/j15-Transpose.xlsx

 

[jeffreyweir]

Something very similar to what you want can be accomplished with a mix of formulas and pivot tables. See https://www.dropbox.com/s/3o14bm4ss2kwi8n/j15-Transpose%20v2.xlsx


However, the difference is that multiple values appear in adjacent cells, and not in the same cell as your question asks. But they are visually distinct, so this might be fine for your purposes.