Excel fields in a row instead of column...

[doran_doran]

Hi,

I am back, LOL. Well, I have an Excel file but the data are in it looks weired. I can't sort and do nothing. Column A has Title (Field Name) about 13 Titles. Column B Contains the data. How can I convert or import this data in access table. I haven't create a table with the same fields in Excel. There is blank line between each records. So say record one has 13 row so second records starts from the 15th row and has 13 row for this record. Any idea. I have never received a file like this.

PLEASE PLEASE PLEASE HELP FIND A LINK OR IDEA HOW DO I DO THIS.

MANY THANKS IN ADVANCE. Please see the print screen for clarity...

Thanks
Sarah Martinez

[ExcelGirl]

Hi Sarah,

I assume you want the 13 titles in the first record to be column headings across the top with the appropriate data under each title.

To do that, you select the range A1 to B13 and copy it. Select the cell where you want the reorganized data to display and right-click, choose Paste All, Operatin None and check Transpose. Then click OK.

How is that for a start?

ExcelGirl

[doran_doran]

Hi Excel Girl,

I tried that and it's working for one record. Actually it works for multiple records but it's the second or third record after 13th column then so forth. hmm any other ideas??

[ExcelGirl]

Sarah,

I don't know what you mean by:

"but it's the second or third record after 13th column then so forth."

Does this not work for you for the 2nd & 3rd records?

ExcelGirl

[doran_doran]

Hi,

for example I have 10 records (130 row, each record with 13 rows, 13 different fields).

I have attached about 10/12 records in the attach file. please check it out..

thanks

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>

[Roger Govier]

Hi

If I understand your problem correctly, then column a contains your field
names, repeated for each record.
Copy A1:A13 and Paste Special>Transpose to cell E1.
Your Field names will now be listed in E1:Q1

In cell E2 enter the following formula
=OFFSET($E$1,(COLUMN()-5+(ROW()-2)*14),-3)
Copy this formula across through cells F2:Q2
Then copy E2:Q2 down through as many rows as you require and this will pull
in the numeric values from each record

--
Regards
Roger Govier
"doran_doran" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Excel Girl,
>
> I tried that and it's working for one record. Actually it works for
> multiple records but it's the second or third record after 13th column
> then so forth. hmm any other ideas??
>
>
> --
> doran_doran
> ------------------------------------------------------------------------
> doran_doran's Profile:
> http://www.excelforum.com/member.php...o&userid=25274
> View this thread: http://www.excelforum.com/showthread...hreadid=387655
>