J
Jason
I have four ranges of cells on separate rows. I want to be able to
consolidate groups of identical items from these rows into a list in a
column. In addition I need to sum the numbers associated with the
item (numbers are directly below the item in case that helps)
A visual simplification:
Col A Col B Col C Col D
R 1 apple apple apple pear
R 2 5.5 5.5 5.5 3
R 3
R 4 pear dog dog rabbit
R 5 3 5 5 6
R 6
R 7 apple apple apple apple
R 8 5.5 5.5 5.5 5.5
R 9
R 10 cat apple cat cat
R 11 2 5.5 2 2
R 12
Would consolidate by way of a macro (or perhaps a trick array
formula?) into:
Col A Col B
R 50 Item Sum
R 51 apple 16.5
R 52 pear 3
R 53 pear 3
R 54 dog 10
R 55 rabbit 6
R 56 apple 22
R 57 cat 2
R 58 apple 5.5
R 59 cat 4
Where one row ends in a item that is the same as the first item on the
next row (eg pear on rows 1 and 4) the item appears twice and is not
treated as a 'group'.
I appreciate that providing a complete solution would be a huge ask,
but I thought I'd throw it out there to see what advice I could get.
I've searched these newsgroups and have found valuable chunks of code
and useful formulae, but I have been unable to combine this to work
for my scenario.
Any words of wisdom are appreciated.
consolidate groups of identical items from these rows into a list in a
column. In addition I need to sum the numbers associated with the
item (numbers are directly below the item in case that helps)
A visual simplification:
Col A Col B Col C Col D
R 1 apple apple apple pear
R 2 5.5 5.5 5.5 3
R 3
R 4 pear dog dog rabbit
R 5 3 5 5 6
R 6
R 7 apple apple apple apple
R 8 5.5 5.5 5.5 5.5
R 9
R 10 cat apple cat cat
R 11 2 5.5 2 2
R 12
Would consolidate by way of a macro (or perhaps a trick array
formula?) into:
Col A Col B
R 50 Item Sum
R 51 apple 16.5
R 52 pear 3
R 53 pear 3
R 54 dog 10
R 55 rabbit 6
R 56 apple 22
R 57 cat 2
R 58 apple 5.5
R 59 cat 4
Where one row ends in a item that is the same as the first item on the
next row (eg pear on rows 1 and 4) the item appears twice and is not
treated as a 'group'.
I appreciate that providing a complete solution would be a huge ask,
but I thought I'd throw it out there to see what advice I could get.
I've searched these newsgroups and have found valuable chunks of code
and useful formulae, but I have been unable to combine this to work
for my scenario.
Any words of wisdom are appreciated.