Sumproduct of unique values in multiple ranges

[Dan5977]

Hi guys,

Hope you can help. I have a rather complicated (by my standards) spreadsheet and am stuck at this point. Example of type of problem shown below.

So I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.

I'm using Excel 2010.

As always, any assistance would be appreciated.

Thanks,

Dan.

 

[Aladin Akyurek]

Thanks guys.

That last one in particular looks pretty impressive stuff and appears to solve my problem. I'll try to work it into my ever developing spreadsheet over the next few days.

Thanks so much!

You are welcome.

 

[Dan5977]

Qty	Length	Qty	Length	Qty	Length
1	3500	1	3000	1	980
		2	3020	1	1000
		2	3020	1	1000
2	3500			1	1243
				2	2500
1	3500
7
Desired Result
4	3500
1	3000
4	3020
1	980
2	1000
1	1243
2	2500

<tbody>
</tbody>

Add the following code for ARRAYUNION function to your workbook, using Alt+F11.

Now define LenghtData by means of Formulas | Name Manager as referring to:

=arrayunion(Sheet1!$B$2:$B$7,Sheet1!$D$2:$D$4,Sheet1!$F$2:$F$6)

And define Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(LengthData)))

A11, just enter and copy down:

=IF($B11="","",SUMIF($B$2:$F$7,$B11,$A$2:$E$6))

B11, control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$11:B11)<=$A$9, INDEX(LengthData,
  SMALL(IF(FREQUENCY(IF(LengthData<>"", 
  MATCH("~"&LengthData,LengthData&"",0)),Ivec),Ivec),
  ROWS($B$11:B11))),"")

Addendum. See the workbook that implements the set up of above:
https://dl.dropboxusercontent.com/u/65698317/Dan5977 unque items of a multicolumn range.xlsm

My FREQUENCY CALC in equivalent to $A$9 on the spreadsheet is returning a #NAME? error. I copied Aladin's formula from that cell on the dummy sheet and adjusted the lookup cell accordingly.

Could this be a version issue? I'm using Excel2010. My best guess is that it doesn't know what arrayunion refers to.

Any ideas?

 

[Aladin Akyurek]

My FREQUENCY CALC in equivalent to $A$9 on the spreadsheet is returning a #NAME? error. I copied Aladin's formula from that cell on the dummy sheet and adjusted the lookup cell accordingly.

Could this be a version issue? I'm using Excel2010. My best guess is that it doesn't know what arrayunion refers to.

Any ideas?

I have no idea what you have been doing. Did you add the ARRAYUNION code to your workbook if you are not using the one I posted?

 

[Dan5977]

I have no idea what you have been doing. Did you add the ARRAYUNION code to your workbook if you are not using the one I posted?

Yes. I copied it from the workbook you kindly made into mine. It feels like I'm so close. All the formulas and arrays are in and amended to suit the actual cells. There must just be one issue causing the #NAME? error. I think it relates to arrayunion. It's either that or LengthData but LengthData is defined as you stated too. Everything is spelt correctly and consistently.

Hopefully with a fresh start in the morning it will be more obvious for me!

 

[Aladin Akyurek]

Yes. I copied it from the workbook you kindly made into mine. It feels like I'm so close. All the formulas and arrays are in and amended to suit the actual cells. There must just be one issue causing the #NAME? error. I think it relates to arrayunion. It's either that or LengthData but LengthData is defined as you stated too. Everything is spelt correctly and consistently.

Hopefully with a fresh start in the morning it will be more obvious for me!

Run Alt+F11.
Choose This Workbook.
Run Insert | Module
Copy the VBA code for ARRAYUNION and paste it into the module pane.
Run File | Close and Return to Microsoft Excel.

If #NAME? still occurs, some definition must be missing.

 

[Dan5977]

It works!

Thanks so much for your help and your patience. It's really appreciated.

 

[Aladin Akyurek]

It works!

Thanks so much for your help and your patience. It's really appreciated.

You are welcome. Great to hear everything is ok.