I have a large sheet of about 12,000 rows that I need to reformat to
add to an Access Data table. My Excel sheet has sales volume by month
(for multile IDs and customers) vertically. I would like to take this
data and make the months columns so that my rows are only about 400
instead of 12,000. I can create a pivot table, but it groups the
items where there are multiple products or IDs for one customer and
therefore it doesn't work for my Access table. Is there a way to
create a pivot table to re-sort my data in this way without making
goupings?

So instead of something like this:

Customer_Name Customer_ID Product Jan_06 Feb_06
Customer 4 33335 Product 1 92 85
Product 2
55555 Product 1 0 143
54446 Product 1
66443 Product 1 0 0
557989 Product 1

It would look more like this:

Customer_Name Customer_ID Product Jan_06 Feb_06

Customer 4 33335 Product 1 92 85
Customer 4 33335 Product 2
Customer 4 55555 Product 1 0 143
Customer 4 54446 Product 1
Customer 4 66443 Product 1 0 0
Customer 4 557989 Product 1

You could copy that data to a new worksheet and paste as values.

Then manipulate it to "fill in the blanks".

Debra Dalgleish shows how:
http://contextures.com/xlDataEntry02.html

" wrote:

I have a large sheet of about 12,000 rows that I need to reformat to
add to an Access Data table. My Excel sheet has sales volume by month
(for multile IDs and customers) vertically. I would like to take this
data and make the months columns so that my rows are only about 400
instead of 12,000. I can create a pivot table, but it groups the
items where there are multiple products or IDs for one customer and
therefore it doesn't work for my Access table. Is there a way to
create a pivot table to re-sort my data in this way without making
goupings?

So instead of something like this:

Customer_Name Customer_ID Product Jan_06 Feb_06
Customer 4 33335 Product 1 92 85
Product 2
55555 Product 1 0 143
54446 Product 1
66443 Product 1 0 0
557989 Product 1

It would look more like this:

Customer_Name Customer_ID Product Jan_06 Feb_06

Customer 4 33335 Product 1 92 85
Customer 4 33335 Product 2
Customer 4 55555 Product 1 0 143
Customer 4 54446 Product 1
Customer 4 66443 Product 1 0 0
Customer 4 557989 Product 1

--

Dave Peterson

You can even try double clicking on the pivot coumns/data/row bar and
selecting "None" in subtotals.

"Dave Peterson" wrote:

You could copy that data to a new worksheet and paste as values.

Then manipulate it to "fill in the blanks".

Debra Dalgleish shows how:
http://contextures.com/xlDataEntry02.html

" wrote:

I have a large sheet of about 12,000 rows that I need to reformat to
add to an Access Data table. My Excel sheet has sales volume by month
(for multile IDs and customers) vertically. I would like to take this
data and make the months columns so that my rows are only about 400
instead of 12,000. I can create a pivot table, but it groups the
items where there are multiple products or IDs for one customer and
therefore it doesn't work for my Access table. Is there a way to
create a pivot table to re-sort my data in this way without making
goupings?

So instead of something like this:

Customer_Name Customer_ID Product Jan_06 Feb_06
Customer 4 33335 Product 1 92 85
Product 2
55555 Product 1 0 143
54446 Product 1
66443 Product 1 0 0
557989 Product 1

It would look more like this:

Customer_Name Customer_ID Product Jan_06 Feb_06

Customer 4 33335 Product 1 92 85
Customer 4 33335 Product 2
Customer 4 55555 Product 1 0 143
Customer 4 54446 Product 1
Customer 4 66443 Product 1 0 0
Customer 4 557989 Product 1

--

Dave Peterson

That'll get rid of the subtotal lines (very nice to do before converting to
values), but what about filling the cells in the first column with "customer 4"
(and the rest).

Vivek Shah wrote:

You can even try double clicking on the pivot coumns/data/row bar and
selecting "None" in subtotals.

"Dave Peterson" wrote:

You could copy that data to a new worksheet and paste as values.

Then manipulate it to "fill in the blanks".

Debra Dalgleish shows how:
http://contextures.com/xlDataEntry02.html

" wrote:

I have a large sheet of about 12,000 rows that I need to reformat to
add to an Access Data table. My Excel sheet has sales volume by month
(for multile IDs and customers) vertically. I would like to take this
data and make the months columns so that my rows are only about 400
instead of 12,000. I can create a pivot table, but it groups the
items where there are multiple products or IDs for one customer and
therefore it doesn't work for my Access table. Is there a way to
create a pivot table to re-sort my data in this way without making
goupings?

So instead of something like this:

Customer_Name Customer_ID Product Jan_06 Feb_06
Customer 4 33335 Product 1 92 85
Product 2
55555 Product 1 0 143
54446 Product 1
66443 Product 1 0 0
557989 Product 1

It would look more like this:

Customer_Name Customer_ID Product Jan_06 Feb_06

Customer 4 33335 Product 1 92 85
Customer 4 33335 Product 2
Customer 4 55555 Product 1 0 143
Customer 4 54446 Product 1
Customer 4 66443 Product 1 0 0
Customer 4 557989 Product 1

--

Dave Peterson


--

Dave Peterson