Find total of col with varying lengths

[csimonds]

Hello Experts.
I have a question which I thought would be straight forward, but is proving quite difficult to solve. I have a monthly spreadsheet prepared by a 3rd party software package that I cannot alter. It returns in col A employee name, followed by product categories. In col. B it returns the value of sales for each of the product categories. As the end of each employee in col. B there is a subtotal. The trouble is col. A product categories vary, only categories with sales are shown. Therefore if an employee does not make a sale in one category it will not be displayed.

I need to be able to extract the employee name and total sale. I have tried various lookups/offset and index but have been unable to come to a workable solution. Would be more than happy for a formula or VBA solution. Any assistance would be greatly appreciated.

Thanks
Carolyn

Original Data Format

Data	Amount
M. Smith
Apple	20
Pear	20
Banana	40
	80
P. Noone
Apple	10
Pear	10
Banana	10
Orange	20
	50
C. Someone
Apple	20
Pear	20
Banana	20
	60

<tbody>
</tbody>

 

[Kemmuniemans]

Does each employee return multiple times within the output? What is the values you want returned according to above data?
Just: M. Smith - 80
P. Noone - 50
C. Someone - 60
???

It's not entirely clear what you want out of it for me.

 

[Aladin Akyurek]

Hello Experts.
I have a question which I thought would be straight forward, but is proving quite difficult to solve. I have a monthly spreadsheet prepared by a 3rd party software package that I cannot alter. It returns in col A employee name, followed by product categories. In col. B it returns the value of sales for each of the product categories. As the end of each employee in col. B there is a subtotal. The trouble is col. A product categories vary, only categories with sales are shown. Therefore if an employee does not make a sale in one category it will not be displayed.

I need to be able to extract the employee name and total sale. I have tried various lookups/offset and index but have been unable to come to a workable solution. Would be more than happy for a formula or VBA solution. Any assistance would be greatly appreciated.

Thanks
Carolyn

[...]

Data	Amount		Employee	Total
M. Smith			M. Smith	80
Apple	20		P. Noone	50
Pear	20		C. Someone	60
Banana	40
	80
P. Noone
Apple	10
Pear	10
Banana	10
Orange	20
	50
C. Someone
Apple	20
Pear	20
Banana	20
	60

<COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4408" width=124><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4465" width=126><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4465" width=126><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2759" width=78><TBODY>
</TBODY>

Data is located in A2:B19, the header row excluded.

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

=IFERROR(INDEX($A$2:$A$19,SMALL(IF(1-($A$2:$A$19=""),
  IF(1-ISNUMBER($B$2:$B$19),ROW($A$2:$A$19)-ROW($A$2)+1)),
  ROWS($D$2:D2))),"")

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

=IF($D2="","",INDEX($B$2:$B$19,SMALL(IF($A$2:$A$19="",
  IF(ISNUMBER($B$2:$B$19),ROW($A$2:$A$19)-ROW($A$2)+1)),
  ROWS($E$2:E2))))

 

[csimonds]

Hi Aladin,

Thank you very much for taking the time to reply. Your formula works perfectly.
Thank you!

 

[Aladin Akyurek]

Hi Aladin,

Thank you very much for taking the time to reply. Your formula works perfectly.
Thank you!

You are welcome. Thanks for providing feedback.