Populate cells based on criteria

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
679
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I'm looking for a solution to the following if possible:
Is there a formula I can put into Column G (from G4 down) that will return what you can see in the picture? The formula is populating based on the criteria in Range C4:D8 and putting a blank cell between each animal type. A single formula would be great but I can work with intermediate helper cells/formula if necessary. I can work with a VBA solution if necessary but would like to see if there's a formula solution first (any VBA solution can't add rows).
Any help much appreciated.
Capture4.JPG
 
How about
Fluff.xlsm
ABCDEFG
1
2
3
4Swan30Swan
5Goat54Swan
6Duck210Swan
7Dog113 
8Cat315Goat
9 Goat
10 Goat
11Goat
12Goat
13 
14Duck
15Duck
16 
17Dog
18 
19Cat
20 
21 
Summary
Cell Formulas
RangeFormula
E4:E10E4=IF(D4="","",SUM(D$3:D3)+ROWS(E$4:E4)-1)
G4:G21G4=IF(OR(ISNUMBER(MATCH(ROWS(G$4:G4),$E$4:$E$10,0)),ROWS(G$4:G4)>MAX($E$4:$E$10)+1),"",INDEX($C$4:$C$10,MATCH(ROWS(G$4:G4),$E$4:$E$10,1)))
I've just thought of an added complication which I should have mentioned at the onset but didn't consider it - one of the entries in Col D could be 0 - in which case the animal should be completely ignored in the result
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
This will handle the 0 values
Fluff.xlsm
ABCDEFG
1
2
3
4Swan30Swan
5Goat54Swan
6Duck010Swan
7Dog110 
8Cat312Goat
9 Goat
10 Goat
11Goat
12Goat
13 
14Dog
15 
16Cat
17 
18 
19 
Summary
Cell Formulas
RangeFormula
E4:E10E4=IF(D4="","",SUM(D$3:D3)+COUNTIFS(D$3:D3,">0"))
G4:G19G4=IF(OR(ISNUMBER(MATCH(ROWS(G$4:G4),$E$4:$E$10,0)),ROWS(G$4:G4)>MAX($E$4:$E$10)+1),"",INDEX($C$4:$C$10,MATCH(ROWS(G$4:G4),$E$4:$E$10,1)))
 
Upvote 0
Solution
This will handle the 0 values
Fluff.xlsm
ABCDEFG
1
2
3
4Swan30Swan
5Goat54Swan
6Duck010Swan
7Dog110 
8Cat312Goat
9 Goat
10 Goat
11Goat
12Goat
13 
14Dog
15 
16Cat
17 
18 
19 
Summary
Cell Formulas
RangeFormula
E4:E10E4=IF(D4="","",SUM(D$3:D3)+COUNTIFS(D$3:D3,">0"))
G4:G19G4=IF(OR(ISNUMBER(MATCH(ROWS(G$4:G4),$E$4:$E$10,0)),ROWS(G$4:G4)>MAX($E$4:$E$10)+1),"",INDEX($C$4:$C$10,MATCH(ROWS(G$4:G4),$E$4:$E$10,1)))
That works great, many thanks for the solution :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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