Getting Cell Values Changing Frequently and Rearranging it in a Compact Manner

[maeyks]

Hello,

I have a table with values and creates a subtotal of each. For example, column A is for fruits, column B is quantity, column C is unit, column D is date.
On the succeeding rows, these are the following:
apple 5 pieces jan 1
apple 6 pieces jan 2
apple 7 pieces jan 3
apple 8 pieces jan 4
and on the next row
Total apple (which is a merged cell from A to C and column D is for the quantity 26 pieces
The next rows are for a different fruit, basically tabulating the same. And showing the subtotal of the next fruit at the end.
what I would like to do is to capture the sub total of each fruits by rows
so i would have
Total apple 26 pieces
Total banana 20 pieces
Total orange 13 pieces

since this will read a dynamic value so the rows for each fruits may vary, it can be 3 rows or even 10 rows depending on the dates

pivot table is not possible since there are merged cells.

so basically I would like to extract the subtotal of each fruits.

hope somebody can assist.

thank you.


michael

 

[frabulator]

I am sure there is probably a more elegant way to do this without VBA, but I am a VBA junkie...

I would loop through each row and break them down into sub categories with collections (or list). Have a list for items, then have a list for quantities, both should have matching index numbers. That way, when you call them, you can use something like this:

Dim N as string
N = Fruits(1)

Dim Q as Long
Q = Quant(1)

Msgbox("Number of " & N & " is " & Q)

Like I said, I am sure there is a more elegant way (and faster as this would take a while to program and execute) but that would be my solution.

It would also help of you could possibly post an example of the data.

 

[maeyks]

I am sure there is probably a more elegant way to do this without VBA, but I am a VBA junkie...

I would loop through each row and break them down into sub categories with collections (or list). Have a list for items, then have a list for quantities, both should have matching index numbers. That way, when you call them, you can use something like this:

Dim N as string
N = Fruits(1)

Dim Q as Long
Q = Quant(1)

Msgbox("Number of " & N & " is " & Q)

Like I said, I am sure there is a more elegant way (and faster as this would take a while to program and execute) but that would be my solution.

It would also help of you could possibly post an example of the data.

This is a sample work sheet.

https://docs.google.com/spreadsheets/d/1_9aioFIihmugr4W1zyQRpXL2qzobFNrFhV6adsdNZ4A/edit?usp=drivesdk

I want to capture rows 12, 24 and 36.

Though this can change since if I paste.a new set of records, it may not be on the same rows again. But basically that is the idea, to get the rows for its total.

Thank you.

 

[Peter_SSs]

This is a sample work sheet.

The link does not work for me. It says "You need access".

Also, is this Google Sheets question or a Microsoft Excel question?

 

[maeyks]

The link does not work for me. It says "You need access".

Also, is this Google Sheets question or a Microsoft Excel question?

For excel. I cannot upload a screenshot it says file is too big

 

[maeyks]

I was able to compress an image...

 

[Peter_SSs]

For excel.

OK thanks. In that case I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, it would help if you could provide your small sample data with XL2BB so that helpers can easily copy for testing without having to manually type everything out.

 

[maeyks]

OK thanks. In that case I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, it would help if you could provide your small sample data with XL2BB so that helpers can easily copy for testing without having to manually type everything out.

Thanks!