preparing/transferring data to database
 

I have an excel spreadsheet with two colomns I needed. The data in those
columns is presented as follows:

Product Equipment
6OL 1702/1701
6OL/LHH 1716
LHH/6OL/SOL 1720
SOL 1701 To 1716

The product code is always 3 digits (but it can be number and letter) and
equipment code is always 4 digits (numbers). I have a database table the
Product list and another table with the Equipment list.

In the excell spreadsheet, the Product column can include one product or
several products through /. The same is for the Equipment column, in addition
it can be as 1701 To 1716.

How could I prepare this data for database usage using some formulars or
queris? Probably, it can be done in an additional excel spreadsheet first
extracting all possible combinations of products and equipment and grouping
it.

Finally, it should be like this:

Product Equipment
6OL 1702
6OL 1701
LHH 1716
LHH 1720
.....

Thanks

preparing/transferring data to database
 

Alex,

You'll need to be more specific about your exact requirements.

Eg. does
SOL 1701 To 1716
translate to
SOL 1701
SOL 1702
......
SOL 1716
?

Does
LHH/6OL/SOL 1720
map to
LHH 1720
6OL 1720
SOL 1720
?

Tim

--
Tim Williams
Palo Alto, CA


"Alex" wrote in message
...
I have an excel spreadsheet with two colomns I needed. The data in those
columns is presented as follows:

Product Equipment
6OL 1702/1701
6OL/LHH 1716
LHH/6OL/SOL 1720
SOL 1701 To 1716

The product code is always 3 digits (but it can be number and letter) and
equipment code is always 4 digits (numbers). I have a database table the
Product list and another table with the Equipment list.

In the excell spreadsheet, the Product column can include one product or
several products through /. The same is for the Equipment column, in
addition
it can be as 1701 To 1716.

How could I prepare this data for database usage using some formulars or
queris? Probably, it can be done in an additional excel spreadsheet first
extracting all possible combinations of products and equipment and
grouping
it.

Finally, it should be like this:

Product Equipment
6OL 1702
6OL 1701
LHH 1716
LHH 1720
....

Thanks

preparing/transferring data to database
 

Thanks a lot for your response, Tim. You are correct that is exactly what I
need.

"Tim Williams" wrote:

Alex,

You'll need to be more specific about your exact requirements.

Eg. does
SOL 1701 To 1716
translate to
SOL 1701
SOL 1702
......
SOL 1716
?

Does
LHH/6OL/SOL 1720
map to
LHH 1720
6OL 1720
SOL 1720
?

Tim

--
Tim Williams
Palo Alto, CA


"Alex" wrote in message
...
I have an excel spreadsheet with two colomns I needed. The data in those
columns is presented as follows:

Product Equipment
6OL 1702/1701
6OL/LHH 1716
LHH/6OL/SOL 1720
SOL 1701 To 1716

The product code is always 3 digits (but it can be number and letter) and
equipment code is always 4 digits (numbers). I have a database table the
Product list and another table with the Equipment list.

In the excell spreadsheet, the Product column can include one product or
several products through /. The same is for the Equipment column, in
addition
it can be as 1701 To 1716.

How could I prepare this data for database usage using some formulars or
queris? Probably, it can be done in an additional excel spreadsheet first
extracting all possible combinations of products and equipment and
grouping
it.

Finally, it should be like this:

Product Equipment
6OL 1702
6OL 1701
LHH 1716
LHH 1720
....

Thanks

preparing/transferring data to database
 

Create a couple of generic functions, one to split "/"-separated cell
values, the other to split cells values containing "TO". Both would
return arrays: in the second case (X to Y) it would be an array of all
numbers from the first to the second number.

Once you have these you can parse each pair of cells into two arrays
and list all combinations of the two arrays. If you only want
distinct combinations then just sort the list and extract the unique
pairs.

Tim.





"Alex" wrote in message
...
Thanks a lot for your response, Tim. You are correct that is exactly
what I
need.

"Tim Williams" wrote:

Alex,

You'll need to be more specific about your exact requirements.

Eg. does
SOL 1701 To 1716
translate to
SOL 1701
SOL 1702
......
SOL 1716
?

Does
LHH/6OL/SOL 1720
map to
LHH 1720
6OL 1720
SOL 1720
?

Tim

--
Tim Williams
Palo Alto, CA


"Alex" wrote in message
...
I have an excel spreadsheet with two colomns I needed. The data
in those
columns is presented as follows:

Product Equipment
6OL 1702/1701
6OL/LHH 1716
LHH/6OL/SOL 1720
SOL 1701 To 1716

The product code is always 3 digits (but it can be number and
letter) and
equipment code is always 4 digits (numbers). I have a database
table the
Product list and another table with the Equipment list.

In the excell spreadsheet, the Product column can include one
product or
several products through /. The same is for the Equipment column,
in
addition
it can be as 1701 To 1716.

How could I prepare this data for database usage using some
formulars or
queris? Probably, it can be done in an additional excel
spreadsheet first
extracting all possible combinations of products and equipment
and
grouping
it.

Finally, it should be like this:

Product Equipment
6OL 1702
6OL 1701
LHH 1716
LHH 1720
....

Thanks

preparing/transferring data to database
 

Thanks, Tim.
I need only unique combinations.
Could you please be more specific - what functions you mean and how I could
apply them in my case? Can I use MID for extracting? But, I have no idea how
to split the numbers.


"Tim Williams" wrote:

Create a couple of generic functions, one to split "/"-separated cell
values, the other to split cells values containing "TO". Both would
return arrays: in the second case (X to Y) it would be an array of all
numbers from the first to the second number.

Once you have these you can parse each pair of cells into two arrays
and list all combinations of the two arrays. If you only want
distinct combinations then just sort the list and extract the unique
pairs.

Tim.





"Alex" wrote in message
...
Thanks a lot for your response, Tim. You are correct that is exactly
what I
need.

"Tim Williams" wrote:

Alex,

You'll need to be more specific about your exact requirements.

Eg. does
SOL 1701 To 1716
translate to
SOL 1701
SOL 1702
......
SOL 1716
?

Does
LHH/6OL/SOL 1720
map to
LHH 1720
6OL 1720
SOL 1720
?

Tim

--
Tim Williams
Palo Alto, CA


"Alex" wrote in message
...
I have an excel spreadsheet with two colomns I needed. The data
in those
columns is presented as follows:

Product Equipment
6OL 1702/1701
6OL/LHH 1716
LHH/6OL/SOL 1720
SOL 1701 To 1716

The product code is always 3 digits (but it can be number and
letter) and
equipment code is always 4 digits (numbers). I have a database
table the
Product list and another table with the Equipment list.

In the excell spreadsheet, the Product column can include one
product or
several products through /. The same is for the Equipment column,
in
addition
it can be as 1701 To 1716.

How could I prepare this data for database usage using some
formulars or
queris? Probably, it can be done in an additional excel
spreadsheet first
extracting all possible combinations of products and equipment
and
grouping
it.

Finally, it should be like this:

Product Equipment
6OL 1702
6OL 1701
LHH 1716
LHH 1720
....

Thanks