Find total of col with varying lengths

C

csimonds

Board Regular
Joined
Oct 2, 2011
Messages
73
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

[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Data
[/TD]
[TD="class: td1"]Amount
[/TD]
[/TR]
[TR]
[TD="class: td1"]M. Smith
[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td1"]Apple
[/TD]
[TD="class: td1"]20
[/TD]
[/TR]
[TR]
[TD="class: td1"]Pear
[/TD]
[TD="class: td1"]20
[/TD]
[/TR]
[TR]
[TD="class: td1"]Banana
[/TD]
[TD="class: td1"]40
[/TD]
[/TR]
[TR]
[TD="class: td1"]

[/TD]
[TD="class: td1"]80
[/TD]
[/TR]
[TR]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td1"]P. Noone
[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td1"]Apple
[/TD]
[TD="class: td1"]10
[/TD]
[/TR]
[TR]
[TD="class: td1"]Pear
[/TD]
[TD="class: td1"]10
[/TD]
[/TR]
[TR]
[TD="class: td1"]Banana
[/TD]
[TD="class: td1"]10
[/TD]
[/TR]
[TR]
[TD="class: td1"]Orange
[/TD]
[TD="class: td1"]20
[/TD]
[/TR]
[TR]
[TD="class: td1"]

[/TD]
[TD="class: td1"]50
[/TD]
[/TR]
[TR]
[TD="class: td1"]

[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td1"]C. Someone
[/TD]
[TD="class: td1"]

[/TD]
[/TR]
[TR]
[TD="class: td1"]Apple
[/TD]
[TD="class: td1"]20
[/TD]
[/TR]
[TR]
[TD="class: td1"]Pear
[/TD]
[TD="class: td1"]20
[/TD]
[/TR]
[TR]
[TD="class: td1"]Banana
[/TD]
[TD="class: td1"]20
[/TD]
[/TR]
[TR]
[TD="class: td1"]

[/TD]
[TD="class: td1"]60
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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.
 
Upvote 0
csimonds said:
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

[...]
Click to expand...


[TABLE="width: 387"]
<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>[TR]
[TD="class: xl63, width: 124, bgcolor: white"]Data[/TD]
[TD="class: xl63, width: 126, bgcolor: white"]Amount[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 126, bgcolor: white"]Employee[/TD]
[TD="class: xl63, width: 78, bgcolor: white"]Total[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]M. Smith[/TD]
[TD="class: xl65, width: 126, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 126, bgcolor: white"]M. Smith[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]80[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Apple[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]20[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 126, bgcolor: white"]P. Noone[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]50[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Pear[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]20[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 126, bgcolor: white"]C. Someone[/TD]
[TD="class: xl65, width: 78, bgcolor: white"]60[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Banana[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]40[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"] [/TD]
[TD="class: xl65, width: 126, bgcolor: white"]80[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"] [/TD]
[TD="class: xl65, width: 126, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]P. Noone[/TD]
[TD="class: xl65, width: 126, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Apple[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]10[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Pear[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]10[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Banana[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]10[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Orange[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]20[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"] [/TD]
[TD="class: xl65, width: 126, bgcolor: white"]50[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"] [/TD]
[TD="class: xl65, width: 126, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]C. Someone[/TD]
[TD="class: xl65, width: 126, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Apple[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]20[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Pear[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]20[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"]Banana[/TD]
[TD="class: xl65, width: 126, bgcolor: white"]20[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 124, bgcolor: white"] [/TD]
[TD="class: xl65, width: 126, bgcolor: white"]60[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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

D2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=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:
Rich (BB code): 
=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))))
 
Upvote 0
Hi Aladin,

Thank you very much for taking the time to reply. Your formula works perfectly.
Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
202
Fluff
Fluff
K
  • Question Question
Formula to find Data and Corresponding date and return total sales and total units per item
Replies
4
Views
178
RoryA
RoryA
M
  • Question Question
Getting Cell Values Changing Frequently and Rearranging it in a Compact Manner
Replies
7
Views
385
maeyks
M
M
  • Question Question
Pivot Table help - Can I show certain data depending on value in another field
Replies
2
Views
882
Monty85
M
B
  • Question Question
Inventory and cost management
Replies
4
Views
509
felixstraube
F

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top