Data Extraction Nightmare- Please help!
 

I am trying to get my data out of one horrible database and ready for data
migration into a decent one. However, when I extract it, the data ends up on
separate rows, instead of in columns:

Matter No.
IN001
Brand
Merchandise
Agent
Block Partners Inc.

How can I get my data to read across instead of down? I've tried exporting
into html, txt, csv, etc, but it's even messier. Assuming Excel is the best
way to get my data out, are there any Excel formulas I can use to reorder my
data? Any help would be much appreciated!

Data Extraction Nightmare- Please help!
 

If you have a single column of data that you want to transpose into a row:

1. select the data
2. copy the data
3. select an un-used cell
4. paste Special transpose
--
Gary''s Student - gsnu200786


"Tami" wrote:

I am trying to get my data out of one horrible database and ready for data
migration into a decent one. However, when I extract it, the data ends up on
separate rows, instead of in columns:

Matter No.
IN001
Brand
Merchandise
Agent
Block Partners Inc.

How can I get my data to read across instead of down? I've tried exporting
into html, txt, csv, etc, but it's even messier. Assuming Excel is the best
way to get my data out, are there any Excel formulas I can use to reorder my
data? Any help would be much appreciated!

Data Extraction Nightmare- Please help!
 

Hi

Why a single row? You can have as much rows and columns, as you like (and
probably you computer can handle). You only have to be sure the destination
range isn't intersecting with source range. The best idea will be to
transpose the table into new empty sheet.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Gary''s Student" wrote in message
...
If you have a single column of data that you want to transpose into a row:

Data Extraction Nightmare- Please help!
 

"Arvi Laanemets" wrote in message
...
Hi

Why a single row? You can have as much rows and columns, as you like (and
probably you computer can handle). You only have to be sure the
destination range isn't intersecting with source range. The best idea will
be to transpose the table into new empty sheet.


I believe he means that each RECORD should end up on one row, not all
records on one row.

Data Extraction Nightmare- Please help!
 

Hi there- Sorry, my example was incorrect.

What I have is:

Matter No.
IN001
Brand Merchandise
Agent Block Partners Inc.
Atty Maria Jones

Matter No.
IN002
Brand Marketing
Agent BCI LLP
Attorney Tracey Lord

What I need is:

Matter No. Brand Agent Attorney
IN001 Merchandise Block Partners Inc. Maria Jones
IN002 Marketing BCI LLP Tracey Lord

I can get a list of matter numbers to run in a column. Is there a function
that I can create (possibly Vlookup) that will compare the actual matter no.
and the column heading (ex: Brand in the column and Brand in the row)? I
have 6000 records that are all messed up and am looking for the quickest way
to recreate the entire table.

Thanks so much for your help!



"JoeSpareBedroom" wrote:

"Arvi Laanemets" wrote in message
...
Hi

Why a single row? You can have as much rows and columns, as you like (and
probably you computer can handle). You only have to be sure the
destination range isn't intersecting with source range. The best idea will
be to transpose the table into new empty sheet.


I believe he means that each RECORD should end up on one row, not all
records on one row.

Data Extraction Nightmare- Please help!
 

Hi

Following is valid, when data structure remains always same, i.e. you have 5
data rows in every entry, separated wit a single empty row, matter number is
always in column A below header, and all other entries in column B, right to
according header. When the structure isn't fixed, then you have to write a
procedure to transfer data.
And I assumed, that source data start from row 1 - otherwise you have to
edit formulas. - and that source data are on sheet Sheet1 (probably you have
to edit the sheet name in formuals)

On a new sheet, enter headers into row 1 (A1:D1)
A2=IF(OFFSET(Sheet1!$A$1;6*(ROW()-2)+1,0)="","",OFFSET(Sheet1!$A$1,6*(ROW()-2)+1,0))
B2=IF($A2="","",OFFSET(Sheet1!$A$1,6*(ROW()-2)+2,1))
C2=IF($A2="","",OFFSET(Sheet1!$A$1,6*(ROW()-2)+3,1))
D2=IF($A2="","",OFFSET(Sheet1!$A$1,6*(ROW()-2)+4,1))

Select A2:D2, and copy down for as much rows as you need.
Copy the range with new table, and overwrite it using PasteSpecial.Values

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Tami" wrote in message
...
Hi there- Sorry, my example was incorrect.

What I have is:

Matter No.
IN001
Brand Merchandise
Agent Block Partners Inc.
Atty Maria Jones

Matter No.
IN002
Brand Marketing
Agent BCI LLP
Attorney Tracey Lord

What I need is:

Matter No. Brand Agent
Attorney
IN001 Merchandise Block Partners Inc. Maria Jones
IN002 Marketing BCI LLP Tracey
Lord

I can get a list of matter numbers to run in a column. Is there a
function
that I can create (possibly Vlookup) that will compare the actual matter
no.
and the column heading (ex: Brand in the column and Brand in the row)? I
have 6000 records that are all messed up and am looking for the quickest
way
to recreate the entire table.

Thanks so much for your help!



"JoeSpareBedroom" wrote:

"Arvi Laanemets" wrote in message
...
Hi

Why a single row? You can have as much rows and columns, as you like
(and
probably you computer can handle). You only have to be sure the
destination range isn't intersecting with source range. The best idea
will
be to transpose the table into new empty sheet.


I believe he means that each RECORD should end up on one row, not all
records on one row.