If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sumproduct with partial charcter matching
I'm trying to achieve the following summary based on the data in the 'Data table' below. I require assitance to construct a sumproduct formula that will aggregate the blood groups correctly (e.g. A + & A - combined as A, AB + & AB - combined as AB). I want to construct one generic formula that will aggregate based on the blood group characters excluding the +/-. Summary Table Combined Blood Groups Data Type Component A AB B O Inventory Red Cell 10 20 40 60 Issues Red Cell 2 4 6 8 Data Table Data Type Component ABO Qty Inventory Red Cell A + 5 Inventory Red Cell A - 5 Inventory Red Cell AB + 10 Inventory Red Cell AB - 10 Inventory Red Cell B + 20 Inventory Red Cell B - 20 Inventory Red Cell O + 30 Inventory Red Cell O - 30 Issues Red Cell A + 1 Issues Red Cell A - 1 Issues Red Cell AB + 2 Issues Red Cell AB - 2 Issues Red Cell B + 3 Issues Red Cell B - 3 Issues Red Cell O + 4 Issues Red Cell O - 4 |
#2
|
|||
|
|||
sumproduct with partial charcter matching
Assume your data table is in sheet: x, with data in A117
Assume your summary table (in another sheet) is set up with C1 across housing the combined blood groups A, AB, B, O and A2:B2 down containing the data type and component, put this in C2: =SUMPRODUCT((x!$A$2:$A$17=$A2)*(x!$B$2:$B$17=$B2)* (TRIM(SUBSTITUTE(SUBSTITUTE(x!$C$2:$C$17,"+",""),"-",""))=C$1),x!$D$2:$D$17) Copy C2 across / fill down for the required results Success? Celebrate it, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Matt" wrote: I'm trying to achieve the following summary based on the data in the 'Data table' below. I require assitance to construct a sumproduct formula that will aggregate the blood groups correctly (e.g. A + & A - combined as A, AB + & AB - combined as AB). I want to construct one generic formula that will aggregate based on the blood group characters excluding the +/-. Summary Table Combined Blood Groups Data Type Component A AB B O Inventory Red Cell 10 20 40 60 Issues Red Cell 2 4 6 8 Data Table Data Type Component ABO Qty Inventory Red Cell A + 5 Inventory Red Cell A - 5 Inventory Red Cell AB + 10 Inventory Red Cell AB - 10 Inventory Red Cell B + 20 Inventory Red Cell B - 20 Inventory Red Cell O + 30 Inventory Red Cell O - 30 Issues Red Cell A + 1 Issues Red Cell A - 1 Issues Red Cell AB + 2 Issues Red Cell AB - 2 Issues Red Cell B + 3 Issues Red Cell B - 3 Issues Red Cell O + 4 Issues Red Cell O - 4 |
#3
|
|||
|
|||
sumproduct with partial charcter matching
Max thankyou very much for the prompt reply - this works perfectly!
"Max" wrote: Assume your data table is in sheet: x, with data in A117 Assume your summary table (in another sheet) is set up with C1 across housing the combined blood groups A, AB, B, O and A2:B2 down containing the data type and component, put this in C2: =SUMPRODUCT((x!$A$2:$A$17=$A2)*(x!$B$2:$B$17=$B2)* (TRIM(SUBSTITUTE(SUBSTITUTE(x!$C$2:$C$17,"+",""),"-",""))=C$1),x!$D$2:$D$17) Copy C2 across / fill down for the required results Success? Celebrate it, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Matt" wrote: I'm trying to achieve the following summary based on the data in the 'Data table' below. I require assitance to construct a sumproduct formula that will aggregate the blood groups correctly (e.g. A + & A - combined as A, AB + & AB - combined as AB). I want to construct one generic formula that will aggregate based on the blood group characters excluding the +/-. Summary Table Combined Blood Groups Data Type Component A AB B O Inventory Red Cell 10 20 40 60 Issues Red Cell 2 4 6 8 Data Table Data Type Component ABO Qty Inventory Red Cell A + 5 Inventory Red Cell A - 5 Inventory Red Cell AB + 10 Inventory Red Cell AB - 10 Inventory Red Cell B + 20 Inventory Red Cell B - 20 Inventory Red Cell O + 30 Inventory Red Cell O - 30 Issues Red Cell A + 1 Issues Red Cell A - 1 Issues Red Cell AB + 2 Issues Red Cell AB - 2 Issues Red Cell B + 3 Issues Red Cell B - 3 Issues Red Cell O + 4 Issues Red Cell O - 4 |
#4
|
|||
|
|||
sumproduct with partial charcter matching
Thats good, you're welcome.
Do click the YES button below in that response, won't you -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Matt" wrote: Max thankyou very much for the prompt reply - this works perfectly! |
Thread Tools | |
Display Modes | |
|
|