Larft
Technical User
- Dec 9, 2002
- 55
I asked this question in the MS Office group but received no replies so I thought I'd try here, since it's probably a VBA resolution anyway.
I am working on a formula calculation spreadsheet in Excel 2003. It utilizes several tables of data that I do Vlookups on multiple columns of data. The data tables are a download from our old system housed on an AS400 which was my starting point. To get a better idea of what I'm dealing with here I'll try explain it as clearly as I can. I have an input sheet that allows you to enter a text "RM" code and a numerical amount. I then have a calculation sheet that pulls this data over, does a lookup on the data tables and does some math on the retrieved lookups from a "Breakdown" and "Constants" table, the "Breakdown" table is the one I'm most concerned with at the moment. Each table has a primary value, the "RM" code, that is looked up in the first column. In the "Breakdown" table there are 4 sets of 16 columns each that I pull the results from (not all of the 16 are populated with data), the first set "Class1-16" is a letter value and is a fixed range of 7 letter values (F,P,E,AN,AV,S,SN) this relates to a class of components, the second and third sets of columns are decimal fractions of 1 for weight (WTF1-16) and volume (VLF1-16) percentages. The fourth set of columns are alphanumeric values (ID1-16) to identify a material code, this is where it gets difficult, the data here are not fixed and could theoretically be any of more than a thousand values and in many cases has multiple occurences of the same value that are components inside of a material. I should also mention that the classes do not all fall in the same column, you can have an "F" or "S" in any position in columns 1-16 and have a material that has only 1 or 2 class components or as many as 16, something like this:
RM1 F S
RM2 S
RM3 F F S S S S AN F SN
So for a material you might see something like this coming from the Breakdown table, this just shows the first 2 of the 16 columns:
Entry Table:
RM WT
A123 100
A213 25
Breakdown lookup result:
RM CLS1 CLS2 WTF1 WTF2 VLF1 VLF2 ID1 ID2
A123 F S .6 .5 .55 .45 R100 S100
A213 F S .5 .5 .45 .65 R200 S100
For each material (RM) the CLS1 column Relates to the WTF1 column, the VLF1 column and the ID1 column then the CLS2 relates to the WTF2...... you get the idea.
I then take the WT value from the entry table and calculate against the results of the lookup.
What I want to be able to do is to consolidate the ID part of the data and do several things with it. First I want to report each of the retrieved values for ID combining the duplicates into one and then sum the related numerical records, like this:
WT VOl
R100 50 40
R200 30 40
S100 20 20
100 100
I've been able sum the values using SUMIF for the product classes, this is easy enough since there are only 7 unique values, but for the ID part under each class there are many possibilities.
Any help would be greatly appreciated, hopefully this explanation isn't too confusing.
I am working on a formula calculation spreadsheet in Excel 2003. It utilizes several tables of data that I do Vlookups on multiple columns of data. The data tables are a download from our old system housed on an AS400 which was my starting point. To get a better idea of what I'm dealing with here I'll try explain it as clearly as I can. I have an input sheet that allows you to enter a text "RM" code and a numerical amount. I then have a calculation sheet that pulls this data over, does a lookup on the data tables and does some math on the retrieved lookups from a "Breakdown" and "Constants" table, the "Breakdown" table is the one I'm most concerned with at the moment. Each table has a primary value, the "RM" code, that is looked up in the first column. In the "Breakdown" table there are 4 sets of 16 columns each that I pull the results from (not all of the 16 are populated with data), the first set "Class1-16" is a letter value and is a fixed range of 7 letter values (F,P,E,AN,AV,S,SN) this relates to a class of components, the second and third sets of columns are decimal fractions of 1 for weight (WTF1-16) and volume (VLF1-16) percentages. The fourth set of columns are alphanumeric values (ID1-16) to identify a material code, this is where it gets difficult, the data here are not fixed and could theoretically be any of more than a thousand values and in many cases has multiple occurences of the same value that are components inside of a material. I should also mention that the classes do not all fall in the same column, you can have an "F" or "S" in any position in columns 1-16 and have a material that has only 1 or 2 class components or as many as 16, something like this:
RM1 F S
RM2 S
RM3 F F S S S S AN F SN
So for a material you might see something like this coming from the Breakdown table, this just shows the first 2 of the 16 columns:
Entry Table:
RM WT
A123 100
A213 25
Breakdown lookup result:
RM CLS1 CLS2 WTF1 WTF2 VLF1 VLF2 ID1 ID2
A123 F S .6 .5 .55 .45 R100 S100
A213 F S .5 .5 .45 .65 R200 S100
For each material (RM) the CLS1 column Relates to the WTF1 column, the VLF1 column and the ID1 column then the CLS2 relates to the WTF2...... you get the idea.
I then take the WT value from the entry table and calculate against the results of the lookup.
What I want to be able to do is to consolidate the ID part of the data and do several things with it. First I want to report each of the retrieved values for ID combining the duplicates into one and then sum the related numerical records, like this:
WT VOl
R100 50 40
R200 30 40
S100 20 20
100 100
I've been able sum the values using SUMIF for the product classes, this is easy enough since there are only 7 unique values, but for the ID part under each class there are many possibilities.
Any help would be greatly appreciated, hopefully this explanation isn't too confusing.