| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Transferring Data | Excel Discussion (Misc queries) | |||
| Preparing Data for Pivot Table | Excel Discussion (Misc queries) | |||
| database query not showing foxpro database How I import data | New Users to Excel | |||
| Reg: Preparing Charts using Pivot Data | Excel Discussion (Misc queries) | |||
| Transferring Data | Excel Discussion (Misc queries) | |||
Linear Mode
