Arranging data

I have an Excel spreadsheet that is imported from a Online orderingsystem,
thus the format is predefined. I need to arrange the data properly in order
to import theese data to Access. I use Office 2003.

Each line starts with a customerID number in column A. I the first row is
listed the different items that has been ordered. One item in each coumn.

If the customer in line 4 has ordered the item listed in column C, the cell
C4 will have the itemID of that item.

The result is a spreadsheet where each customer has severeal items ordered,
but each for each item that is not ordered, the corresponding cell is left
blank.

I need to arrange the data so that each line starts with the customerID (as
now), and all the items is listed from column B onwards removing all the
blanks, and disregarding the item names listed in row 1. Row 1 will be
deleted before the import.

Suggestions?

Try looking here for an initial solution:

http://www.contextures.com/xlDataEntry02.html
Debra Dalgleish's site

--
Regards,
Tom Ogilvy


"Petterq" <Petterq@discussions.microsoft.com> wrote in message
news:84E739FF-0778-4A3C-9EA2-939DCE9FC0BA@microsoft.com...
> I have an Excel spreadsheet that is imported from a Online orderingsystem,
> thus the format is predefined. I need to arrange the data properly in
order
> to import theese data to Access. I use Office 2003.
>
> Each line starts with a customerID number in column A. I the first row is
> listed the different items that has been ordered. One item in each coumn.
>
> If the customer in line 4 has ordered the item listed in column C, the
cell
> C4 will have the itemID of that item.
>
> The result is a spreadsheet where each customer has severeal items
ordered,
> but each for each item that is not ordered, the corresponding cell is left
> blank.
>
> I need to arrange the data so that each line starts with the customerID
(as
> now), and all the items is listed from column B onwards removing all the
> blanks, and disregarding the item names listed in row 1. Row 1 will be
> deleted before the import.
>
> Suggestions?

Hi.

This is something else. On this link i find tips to fill inn blanks, but
that is not the problem.

I need to arrange all celles that have a value from column A and onwards
through B, C and so on. As many columns needed to room all the values. The
number of columns needed will vary from customer to customer.

Example:
CustomerID 8000 in A1
Ordered items: B1:43, D1:55, H1:63

After arranging:
CustomerID 8000 in A1
Ordered items: B1:43, C1:55, D1:63

For on row only it can be done with a sort function, but then I have to sort
one row at a time. I will have spreadsheets with hundreds of rows, and then
it will be to time consuming to take it row by row.

Any suggestions to automate the procedure?

"Tom Ogilvy" wrote:

> Try looking here for an initial solution:
>
> http://www.contextures.com/xlDataEntry02.html
> Debra Dalgleish's site
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Petterq" <Petterq@discussions.microsoft.com> wrote in message
> news:84E739FF-0778-4A3C-9EA2-939DCE9FC0BA@microsoft.com...
> > I have an Excel spreadsheet that is imported from a Online orderingsystem,
> > thus the format is predefined. I need to arrange the data properly in
> order
> > to import theese data to Access. I use Office 2003.
> >
> > Each line starts with a customerID number in column A. I the first row is
> > listed the different items that has been ordered. One item in each coumn.
> >
> > If the customer in line 4 has ordered the item listed in column C, the
> cell
> > C4 will have the itemID of that item.
> >
> > The result is a spreadsheet where each customer has severeal items
> ordered,
> > but each for each item that is not ordered, the corresponding cell is left
> > blank.
> >
> > I need to arrange the data so that each line starts with the customerID
> (as
> > now), and all the items is listed from column B onwards removing all the
> > blanks, and disregarding the item names listed in row 1. Row 1 will be
> > deleted before the import.
> >
> > Suggestions?
>
>
>

Sub RemoveBlanks()
Range("B:AZ").SpecialCells(xlBlanks).Delete Shift:=xlShiftLeft
End Sub

would be another guess.

Test it on a copy of your data.

--
Regards,
Tom Ogilvy


"Petterq" <Petterq@discussions.microsoft.com> wrote in message
news:4BF19F69-5A41-4713-9CE7-C7816C3EA88E@microsoft.com...
> Hi.
>
> This is something else. On this link i find tips to fill inn blanks, but
> that is not the problem.
>
> I need to arrange all celles that have a value from column A and onwards
> through B, C and so on. As many columns needed to room all the values. The
> number of columns needed will vary from customer to customer.
>
> Example:
> CustomerID 8000 in A1
> Ordered items: B1:43, D1:55, H1:63
>
> After arranging:
> CustomerID 8000 in A1
> Ordered items: B1:43, C1:55, D1:63
>
> For on row only it can be done with a sort function, but then I have to
sort
> one row at a time. I will have spreadsheets with hundreds of rows, and
then
> it will be to time consuming to take it row by row.
>
> Any suggestions to automate the procedure?
>
> "Tom Ogilvy" wrote:
>
> > Try looking here for an initial solution:
> >
> > http://www.contextures.com/xlDataEntry02.html
> > Debra Dalgleish's site
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Petterq" <Petterq@discussions.microsoft.com> wrote in message
> > news:84E739FF-0778-4A3C-9EA2-939DCE9FC0BA@microsoft.com...
> > > I have an Excel spreadsheet that is imported from a Online
orderingsystem,
> > > thus the format is predefined. I need to arrange the data properly in
> > order
> > > to import theese data to Access. I use Office 2003.
> > >
> > > Each line starts with a customerID number in column A. I the first row
is
> > > listed the different items that has been ordered. One item in each
coumn.
> > >
> > > If the customer in line 4 has ordered the item listed in column C, the
> > cell
> > > C4 will have the itemID of that item.
> > >
> > > The result is a spreadsheet where each customer has severeal items
> > ordered,
> > > but each for each item that is not ordered, the corresponding cell is
left
> > > blank.
> > >
> > > I need to arrange the data so that each line starts with the
customerID
> > (as
> > > now), and all the items is listed from column B onwards removing all
the
> > > blanks, and disregarding the item names listed in row 1. Row 1 will be
> > > deleted before the import.
> > >
> > > Suggestions?
> >
> >
> >

Hello again.

This looks promissing, however I get an error message that reads something
like:

Run-time error '1004':
The Delete-method in the Range-class was unsuccessful
(translated from Norwegian)

I pasted in the code "as is", only inserted the range "B1:DA519".

Any easy modification? Or is there a way to record the sorting for one row,
and ad a code to the recorded macro to make it repeat it self for a number of
times, always shifting to on row further down the worksheet?

I created the following code:

Sub Sort()

Rows("93:93").Select
Range("B93").Activate
Selection.Sort Key1:=Range("A93"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Rows("94:94").Select
Range("B94").Activate
End Sub

I hid column A, so it is not included in the sorting. I started the record
tool, and started by highligthing a rom (no. 93), made the sort, and moved
down to row 94.

Could this be a way??

Regards
Petter Q.


"Tom Ogilvy" wrote:

> Sub RemoveBlanks()
> Range("B:AZ").SpecialCells(xlBlanks).Delete Shift:=xlShiftLeft
> End Sub
>
> would be another guess.
>
> Test it on a copy of your data.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Petterq" <Petterq@discussions.microsoft.com> wrote in message
> news:4BF19F69-5A41-4713-9CE7-C7816C3EA88E@microsoft.com...
> > Hi.
> >
> > This is something else. On this link i find tips to fill inn blanks, but
> > that is not the problem.
> >
> > I need to arrange all celles that have a value from column A and onwards
> > through B, C and so on. As many columns needed to room all the values. The
> > number of columns needed will vary from customer to customer.
> >
> > Example:
> > CustomerID 8000 in A1
> > Ordered items: B1:43, D1:55, H1:63
> >
> > After arranging:
> > CustomerID 8000 in A1
> > Ordered items: B1:43, C1:55, D1:63
> >
> > For on row only it can be done with a sort function, but then I have to
> sort
> > one row at a time. I will have spreadsheets with hundreds of rows, and
> then
> > it will be to time consuming to take it row by row.
> >
> > Any suggestions to automate the procedure?
> >
> > "Tom Ogilvy" wrote:
> >
> > > Try looking here for an initial solution:
> > >
> > > http://www.contextures.com/xlDataEntry02.html
> > > Debra Dalgleish's site
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Petterq" <Petterq@discussions.microsoft.com> wrote in message
> > > news:84E739FF-0778-4A3C-9EA2-939DCE9FC0BA@microsoft.com...
> > > > I have an Excel spreadsheet that is imported from a Online
> orderingsystem,
> > > > thus the format is predefined. I need to arrange the data properly in
> > > order
> > > > to import theese data to Access. I use Office 2003.
> > > >
> > > > Each line starts with a customerID number in column A. I the first row
> is
> > > > listed the different items that has been ordered. One item in each
> coumn.
> > > >
> > > > If the customer in line 4 has ordered the item listed in column C, the
> > > cell
> > > > C4 will have the itemID of that item.
> > > >
> > > > The result is a spreadsheet where each customer has severeal items
> > > ordered,
> > > > but each for each item that is not ordered, the corresponding cell is
> left
> > > > blank.
> > > >
> > > > I need to arrange the data so that each line starts with the
> customerID
> > > (as
> > > > now), and all the items is listed from column B onwards removing all
> the
> > > > blanks, and disregarding the item names listed in row 1. Row 1 will be
> > > > deleted before the import.
> > > >
> > > > Suggestions?
> > >
> > >
> > >
>
>
>