Distributing values from rows to columns for Access import
 

I have a spreadsheet where i have an customer number in the first column, and
the item number each customer has ordered in the subsequent columns.
In order to import this into a table in Access, I would like to change this
into two columns, where the first column is the customer number, and the
second column is the item number.
The customer number will then be repeated in column A one time for each item
the customer has ordered. A customer ordering 2 items will occupy two rows,
and a customer ordering 10 items will occupy 10 rows. For the Access database
import this will fit with one record in each row.

One way;
Highlight the range and copypaste special transpose
Now assuming the data was pasted starting at A2 Make A3 the active cell and
highlight to last row of data in B. F5SpecialBlanks enter =A2 hold down
Ctrl and press enter.
HTH

"Petterq" wrote:

I have a spreadsheet where i have an customer number in the first column, and
the item number each customer has ordered in the subsequent columns.
In order to import this into a table in Access, I would like to change this
into two columns, where the first column is the customer number, and the
second column is the item number.
The customer number will then be repeated in column A one time for each item
the customer has ordered. A customer ordering 2 items will occupy two rows,
and a customer ordering 10 items will occupy 10 rows. For the Access database
import this will fit with one record in each row.

Hi.

When performing the copypast specialtranspose sequense i get all the
customer numbers in row 1, and the items listed below.
I do not understand the second part of the procedure.
I have some example data to test on, and after the first part I have data
from A1 until AD10.
In my result I should have all data i two columns. Column A with customer
numbers, and column B with item numbers.

Regards
PetterQ

"Ray A" wrote:

One way;
Highlight the range and copypaste special transpose
Now assuming the data was pasted starting at A2 Make A3 the active cell and
highlight to last row of data in B. F5SpecialBlanks enter =A2 hold down
Ctrl and press enter.
HTH

"Petterq" wrote:

I have a spreadsheet where i have an customer number in the first column, and
the item number each customer has ordered in the subsequent columns.
In order to import this into a table in Access, I would like to change this
into two columns, where the first column is the customer number, and the
second column is the item number.
The customer number will then be repeated in column A one time for each item
the customer has ordered. A customer ordering 2 items will occupy two rows,
and a customer ordering 10 items will occupy 10 rows. For the Access database
import this will fit with one record in each row.