Need help with Excel formula to find a group of data and return a value in that data set.

JadeWitte

New Member
Joined
Jun 27, 2012
Messages
6
I have a fairly large spreadsheet with about 5,000 lines and 100+ columns. I've taken a small piece of it and posted below to give you the layout of the spreadsheet. Row 2 is a number assigned to each month over the next 10 years.

What I'm trying to do is write a formula that will look specifically at a column (in this example Column E). I want the formula to scroll down to the first instance of a headcount for all particular grouping (which in the example below, the grouping are separated by the headcount rows). If it is the first time headcount has been introduced for the grouping then the formula will determine which group and the "PGM" amount. In the example below, for column E, The grouping in which there is new headcount added is in row 22 (specifically the trigger is the value in E22, since there is 0 or "" in cell D22). Once I determine the grouping then I need the PGM value which in this example is cell: E28.

I tried doing an HLOOKUP nested within a VLOOKUP, but the problem was it created a circular reference (since certain cells were in each other's arrays). I created a massive IF statement that DID work, but it is extremely cumbersome, and probably not the way to do it. My IF statement basically went down Columns D & E and looked at Rows 4, 13,22, 31...etc. etc..all the way down...until I had this massive formula that nearly maxed out the number of characters for a formula (8,150 characters I think). In my IF statement if Column D was blank AND Column E was greater than 1, then I would request value in E28 in this example below. Is there way to do this without a massive, clumsy nested IF statement? Thank you for your assistance!!!


ABCDEFGHI
1MarAprMayJunJulAugSep
232333435363738
3
4Headcount2223334
5Revenue16,20016,20016,20024,30024,30024,30032,400
6Direct Cost12,08512,08512,08518,12818,12818,12824,170
7DPM4,1154,1154,1156,1726,1726,1728,230
8%DPM25.40%25.40%25.40%25.40%25.40%25.40%25.40%
9Indirect Cost486486486729729729972
10PGM3,6293,6293,6295,4435,4435,4437,258
11%PGM22.40%22.40%22.40%22.40%22.40%22.40%22.40%
12
13Headcount223334
14Revenue015,40015,40023,10023,10023,10030,800
15Direct Cost011,48811,48817,23317,23317,23322,977
16DPM03,9123,9125,8675,8675,8677,823
17%DPM25.40%25.40%25.40%25.40%25.40%25.40%
18Indirect Cost0462462693693693924
19PGM03,4503,4505,1745,1745,1746,899
20%PGM22.40%22.40%22.40%22.40%22.40%22.40%
21
22Headcount22333
23Revenue0015,40015,40023,10023,10023,100
24Direct Cost0011,48811,48817,23317,23317,233
25DPM003,9123,9125,8675,8675,867
26%DPM25.40%25.40%25.40%25.40%25.40%
27Indirect Cost00462462693693693
28PGM003,4503,4505,1745,1745,174
29%PGM22.40%22.40%22.40%22.40%22.40%
30
31Headcount2223
32Revenue00016,20016,20016,20024,300
33Direct Cost00012,08512,08512,08518,128
34DPM0004,1154,1154,1156,172
35%DPM25.40%25.40%25.40%25.40%
36Indirect Cost000486486486729
37PGM0003,6293,6293,6295,443
38%PGM22.40%22.40%22.40%22.40%

<tbody>
</tbody>
 
Last edited:

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
I have a fairly large spreadsheet with about 5,000 lines and 100+ columns. I've taken a small piece of it and posted below to give you the layout of the spreadsheet. Row 2 is a number assigned to each month over the next 10 years.

What I'm trying to do is write a formula that will look specifically at a column (in this example Column E). I want the formula to scroll down to the first instance of a headcount for all particular grouping (which in the example below, the grouping are separated by the headcount rows). If it is the first time headcount has been introduced for the grouping then the formula will determine which group and the "PGM" amount. In the example below, for column E, The grouping in which there is new headcount added is in row 22 (specifically the trigger is the value in E22, since there is 0 or "" in cell D22). Once I determine the grouping then I need the PGM value which in this example is cell: E28.

I tried doing an HLOOKUP nested within a VLOOKUP, but the problem was it created a circular reference (since certain cells were in each other's arrays). I created a massive IF statement that DID work, but it is extremely cumbersome, and probably not the way to do it. My IF statement basically went down Columns D & E and looked at Rows 4, 13,22, 31...etc. etc..all the way down...until I had this massive formula that nearly maxed out the number of characters for a formula (8,150 characters I think). In my IF statement if Column D was blank AND Column E was greater than 1, then I would request value in E28 in this example below. Is there way to do this without a massive, clumsy nested IF statement? Thank you for your assistance!!!


A
B
C
D
E
F
G
H
I
1
Mar
Apr
May
Jun
Jul
Aug
Sep
2
32
33
34
35
36
37
38
3
4
Headcount
2
2
2
3
3
3
4
5
Revenue
16,200
16,200
16,200
24,300
24,300
24,300
32,400
6
Direct Cost
12,085
12,085
12,085
18,128
18,128
18,128
24,170
7
DPM
4,115
4,115
4,115
6,172
6,172
6,172
8,230
8
%DPM
25.40%
25.40%
25.40%
25.40%
25.40%
25.40%
25.40%
9
Indirect Cost
486
486
486
729
729
729
972
10
PGM
3,629
3,629
3,629
5,443
5,443
5,443
7,258
11
%PGM
22.40%
22.40%
22.40%
22.40%
22.40%
22.40%
22.40%
12
13
Headcount
2
2
3
3
3
4
14
Revenue
15,400
15,400
23,100
23,100
23,100
30,800
15
Direct Cost
11,488
11,488
17,233
17,233
17,233
22,977
16
DPM
3,912
3,912
5,867
5,867
5,867
7,823
17
%DPM
25.40%
25.40%
25.40%
25.40%
25.40%
25.40%
18
Indirect Cost
462
462
693
693
693
924
19
PGM
3,450
3,450
5,174
5,174
5,174
6,899
20
%PGM
22.40%
22.40%
22.40%
22.40%
22.40%
22.40%
21
22
Headcount
2
2
3
3
3
23
Revenue
15,400
15,400
23,100
23,100
23,100
24
Direct Cost
11,488
11,488
17,233
17,233
17,233
25
DPM
3,912
3,912
5,867
5,867
5,867
26
%DPM
25.40%
25.40%
25.40%
25.40%
25.40%
27
Indirect Cost
462
462
693
693
693
28
PGM
3,450
3,450
5,174
5,174
5,174
29
%PGM
22.40%
22.40%
22.40%
22.40%
22.40%
30
31
Headcount
2
2
2
3
32
Revenue
16,200
16,200
16,200
24,300
33
Direct Cost
12,085
12,085
12,085
18,128
34
DPM
4,115
4,115
4,115
6,172
35
%DPM
25.40%
25.40%
25.40%
25.40%
36
Indirect Cost
486
486
486
729
37
PGM
3,629
3,629
3,629
5,443
38
%PGM
22.40%
22.40%
22.40%
22.40%

<TBODY>
</TBODY>
So this will address the specific scenario you described, but something tells me it doesn't meet what ALL your needs really are. Perhaps you'll be able to modify it to meet what you really need.

=INDEX($E$1:$E$38,SUMPRODUCT(--($B$1:$B$38="HEADCOUNT")*--($C$1:$C$38="")*--($D$1:$D$38="")*--($E$1:$E$38>1)*A1:A38)+6,1)

Not sure where you need to have this formula place, but for column E:


1) It will look for which row has the heading headcount
2) identify where column C, and Column D = ""
3) identify where column E > 1

Once it has verified which row has headcount and a "" in column C and column D and column E is >1 it will return the row number from column A and add 6 since that is how many rows down PGM is from headcount.

The row number from above is passed into the INDEX function which uses that value to know which row number to pick returning the PGM value from COLUMN E.
 

JadeWitte

New Member
Joined
Jun 27, 2012
Messages
6
Thank you CMS, jr.!!! I've never used the SUMPRODUCT function, but will from now on! Really cool. So absolutely the formula will work the way you wrote it for the example I gave you, however, my example (the way I illustrated it) was not completely accurate. Column A is not a series of numbers 1 to 38, but rather me trying to number the rows for the sake of trying to depict Excel to help solve the problem. In other words Column A is where the data starts (or the column for "Headcount"). Having said that, I could insert a column at the beginning and easily add 1,2,3....all the way down and use your formula as is (modifying the column names, etc). But I would prefer not insert this number column at the front if not necessary...if there is a different way. I'm assuming the formula needs these numbers to know what number to add "6" to in order to get to the "PGM" number, so maybe I have to insert this column. I can always then "hide" it if it looks strange. Curious if there is a different way? Also, what does the "1" indicate at the very end of the formula? Thanks again!! I've spent countless hours trying to figure this out and you have really taught me something new that I'm excited to start leveraging!!!

Regards, Jade
 

JadeWitte

New Member
Joined
Jun 27, 2012
Messages
6
Thanks again. This formula works fairly well for me after doing some modifications as you indicated I might have to do. The challenge that I'm having is that I really need the $E$1:$E$38 array to be dynamic versus static. Here's why. I'm feeding numbers into this spreadsheet from another one. Based upon a VLOOKUP on that other spreadsheet I'm either adding +2, +1, 0, or -2 to the month identifiers on the spreadsheet above. Specifically in this example (34) under May. So let's say, my VLOOKUP on the other spreadsheet returns a (+1). I would add +1 to 34 and the month (column) I would want to use for the INDEX (SUMPRODUCT) formula above is 35 (or the Month of June). If the VLOOK came back with -2, I would subtract 2 from 34 and want the column 32 (or March). I would want to get the PGM number for the data group just like above but I want the column to be dynamic rather than hard-coded as is the case now. Can you or anyone else help me do this, please? I've been researching MATCH, INDEX, V & HLOOKUP functions all day and know that I'm close to getting it. Thank you!!!
 
Last edited:

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
Sorry I didn't respond, had to focus on work today. Take a look at using the OFFSET function. It always you to move ranges or position. So you could utilize the 1, -1, -2 returned from the VLOOKUP withing the OFFSET to know which column , E, F, Etc to be in.
 

JadeWitte

New Member
Joined
Jun 27, 2012
Messages
6
No worries...I've been going down the OFFSET path this afternoon but not sure how to construct the formula exactly. I should hopefully figure it out tonight. But, I'm with you, I think OFFSET is the way to go potentially. Thanks for responding.
 

Forum statistics

Threads
1,082,360
Messages
5,364,939
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top