How to apply a formula a certain number of times based on another cell

G

Grace1918

New Member
Joined
Feb 24, 2020
Messages
13
Office Version
  1. 365
Hey there,

I have two tables that contain all of our companies employees and am looking to count the number of times a certain position occurs in one table and put the adjacent name into a row in another column. I am currently using
VBA Code: 
=IFERROR(INDEX($A:$A,SMALL(IF("Senior Project Director"=$B:$B,ROW($B:$B),""),ROW()-ROW(F2))),"")
and was hoping that someone would know of a way to have a macro drag the formula down the number of cells that certain position occurs.

I have been using Countif to count the positions but I don't know if that applies to VBA. We are always adding new employees to the list so that's why I was hoping it could be dynamic instead of having a long array.

Thanks in advance to anyone who has any ideas.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Can you post a sample of both tables, using the XL2BB add-in?
 
Upvote 0
Hey, I just changed all the names to be the first letter of the name for privacy reasons. I am using the position columns in a data validation in another sheet so that's why I can't just have the formula going on forever. I was previously using the Unique function which was working perfectly for me but others couldn't view the function so I am trying to do this as a work around.


P0## 201# - PROJECT NAME R .0.6 Grace.xlsm
ABCDEFGHIJKLMNOPQ
1
2Employee NameLevelLocationVPSenior Project DirectorSenior Project EngineerProject ManagerElectrical EngineerMechanical EngineerSenior DesignerIntermediate Project EngineerConstruction ManagerEngineer-in-TrainingDesigner/DraftingAdministrationMaterials Contractors
3AIntermediate Project EngineerUS         
4ASenior Project DirectorCDN
5AIntermediate Project EngineerUS
6AEngineer-in-TrainingCDN
7AEngineer-in-TrainingCDN
8ASenior DesignerCDN
9AIntermediate Project EngineerCDN
10ADesigner/DraftingCDN
11AEngineer-in-TrainingCDN
12BVPCDN
13BEngineer-in-TrainingCDN
14BSenior Project DirectorCDN
15BSenior Project EngineerUS
16BSenior Project EngineerCDN
17BDesigner/DraftingCDN
18CDesigner/DraftingCDN
19CIntermediate Project EngineerUS
20CSenior Project DirectorCDN
21CAdministrationCDN
22CEngineer-in-TrainingCDN
23CIntermediate Project EngineerCDN
24DSenior DesignerCDN
25DVPCDN
26DSenior Project DirectorUS
27DSenior Project EngineerCDN
28DEngineer-in-TrainingUS
29EAdministrationCDN
30EIntermediate Project EngineerUS
31EAdministrationCDN
32EDesigner/DraftingCDN
33EEngineer-in-TrainingCDN
34FIntermediate Project EngineerCDN
35FProject ManagerCDN
36FElectrical EngineerCDN
37SConstruction ManagerCDN
38GSenior Project EngineerCDN
39GSenior Project EngineerUS
40GDesigner/DraftingCDN
41GEngineer-in-TrainingCDN
42HMechanical EngineerCDN
43JAdministrationCDN
44JIntermediate Project EngineerCDN
45JSenior Project EngineerCDN
46KDesigner/DraftingCDN
47LSenior Project DirectorCDN
48LEngineer-in-TrainingCDN
49LDesigner/DraftingCDN
50MVPCDN
51MIntermediate Project EngineerCDN
52MSenior Project DirectorUS
53MSenior Project DirectorUS
54MSenior Project DirectorCDN
55MSenior Project DirectorUS
56MElectrical EngineerUS
57MDesigner/DraftingCDN
58MSenior Project EngineerUS
59NIntermediate Project EngineerCDN
60P AdministrationCDN
61P VPUS
62RIntermediate Project EngineerCDN
63RDesigner/DraftingCDN
64RConstruction ManagerCDN
65REngineer-in-TrainingCDN
66REngineer-in-TrainingCDN
67SIntermediate Project EngineerCDN
68SSenior DesignerCDN
69SEngineer-in-TrainingCDN
70SDesigner/DraftingCDN
71SVPCDN
72SSenior DesignerCDN
73TElectrical EngineerCDN
74TAdministrationCDN
75TIntermediate Project EngineerCDN
76VSenior Project EngineerCDN
77WIntermediate Project EngineerUS
78
79
80
81
82
83
84
85
Data (2)
Cell Formulas
RangeFormula
G3,I3:P3G3=IFERROR(INDEX(C:C,SMALL(IF("VP"=D:D,ROW(D:D),""),ROW()-ROW(G2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Named Ranges
NameRefers ToCells
'Data (2)'!Construction_Manager='Data (2)'!$M$3#P3
'Data (2)'!Electrical_Engineer='Data (2)'!$I$3#L3:M3
'Data (2)'!Intermediate_Project_Engineer='Data (2)'!$L$3#O3:P3
'Data (2)'!Mechanical_Engineer='Data (2)'!$J$3#M3:N3
'Data (2)'!Senior_Designer='Data (2)'!$K$3#N3:O3
'Data (2)'!Senior_Project_Engineer='Data (2)'!$G$3#J3:K3
 
Upvote 0
Ok, thanks for that, how about

+Fluff.xlsm
ABCDEFGHIJKLMNOPQ
1
2Employee NameLevelLocationVPSenior Project DirectorSenior Project EngineerProject ManagerElectrical EngineerMechanical EngineerSenior DesignerIntermediate Project EngineerConstruction ManagerEngineer-in-TrainingDesigner/DraftingAdministrationMaterials Contractors
3AIntermediate Project EngineerUSBABFFHAASAACNone
4ASenior Project DirectorCDNDBBMDARABE
5AIntermediate Project EngineerUSMCDTSAACE
6AEngineer-in-TrainingCDNPDGSCBEJ
7AEngineer-in-TrainingCDNSLGCCGP
8ASenior DesignerCDNMJEDKT
9AIntermediate Project EngineerCDNMMFEL
10ADesigner/DraftingCDNMVJGM
11AEngineer-in-TrainingCDNMMLR
12BVPCDNNRS
13BEngineer-in-TrainingCDNRR
14BSenior Project DirectorCDNSS
15BSenior Project EngineerUST
16BSenior Project EngineerCDNW
17BDesigner/DraftingCDN
18CDesigner/DraftingCDN
19CIntermediate Project EngineerUS
20CSenior Project DirectorCDN
21CAdministrationCDN
22CEngineer-in-TrainingCDN
23CIntermediate Project EngineerCDN
24DSenior DesignerCDN
25DVPCDN
26DSenior Project DirectorUS
27DSenior Project EngineerCDN
28DEngineer-in-TrainingUS
29EAdministrationCDN
30EIntermediate Project EngineerUS
31EAdministrationCDN
32EDesigner/DraftingCDN
33EEngineer-in-TrainingCDN
34FIntermediate Project EngineerCDN
35FProject ManagerCDN
36FElectrical EngineerCDN
37SConstruction ManagerCDN
38GSenior Project EngineerCDN
39GSenior Project EngineerUS
40GDesigner/DraftingCDN
41GEngineer-in-TrainingCDN
42HMechanical EngineerCDN
43JAdministrationCDN
44JIntermediate Project EngineerCDN
45JSenior Project EngineerCDN
46KDesigner/DraftingCDN
47LSenior Project DirectorCDN
48LEngineer-in-TrainingCDN
49LDesigner/DraftingCDN
50MVPCDN
51MIntermediate Project EngineerCDN
52MSenior Project DirectorUS
53MSenior Project DirectorUS
54MSenior Project DirectorCDN
55MSenior Project DirectorUS
56MElectrical EngineerUS
57MDesigner/DraftingCDN
58MSenior Project EngineerUS
59NIntermediate Project EngineerCDN
60PAdministrationCDN
61PVPUS
62RIntermediate Project EngineerCDN
63RDesigner/DraftingCDN
64RConstruction ManagerCDN
65REngineer-in-TrainingCDN
66REngineer-in-TrainingCDN
67SIntermediate Project EngineerCDN
68SSenior DesignerCDN
69SEngineer-in-TrainingCDN
70SDesigner/DraftingCDN
71SVPCDN
72SSenior DesignerCDN
73TElectrical EngineerCDN
74TAdministrationCDN
75TIntermediate Project EngineerCDN
76VSenior Project EngineerCDN
77WIntermediate Project EngineerUS
Sheet1
Cell Formulas
RangeFormula
E3:E7,P3:P8,O3:O12,N3:N14,M3:M4,L3:L16,K3:K6,I3:I5,H3,J3,Q3,G3:G10,F3:F11E3=FILTER($A$3:$A$77,$B$3:$B$77=E$2,"None")
Dynamic array formulas.
 
Upvote 0
I tried that before and it worked great for me but not the others. Both Unique and the Filter functions did't work on the other computers and they don't want to change their settings so I'm trying to work around that.
 
Upvote 0
In that case in E3 copied down & across
=IFERROR(INDEX($A$3:$A$77,AGGREGATE(15,6,(ROW($A$3:$A$77)-ROW($A$3)+1)/($B$3:$B$77=E$2),ROWS(E$3:E3))),"")
 
Upvote 0
That is similar to what I had before but I was hoping that the VBA would increase the formula if i added one instead of having blank answers since that makes my data validation too long.

For example I was just curious if a vba could do this
VBA Code: 
    Range("E3").Copy
    Range("E3").Select
    Range(Selection, Selection.Offset(5, 0)).Select
    ActiveSheet.Paste
but instead of having a 5 there replace it with the number that you get from
Code: 
=COUNTIF(Table9[Level],E2)

Thanks for the help
 
Upvote 0
Why not use a dynamic named range?
For VP in col E you could use
=Sheet1!$E$3:INDEX(Sheet1!$E:$E,COUNTIF(Sheet1!$E:$E,"?*")+1)
 
Upvote 0
This seems to work but then when i try to use it for a data validation it doesn't work
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Counting Number of Consecutive Negative/Positive Numbers in a Row
Replies
6
Views
492
Cubist
Cubist
G
  • Question Question
'Storing' and summing data before it is overwritten.
Replies
1
Views
215
gwoolley0302
G
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
371
kbishop94
K
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
219
Ludwig
L
R
  • Question Question
Spill array that only shows rows that have value in certain cells
Replies
1
Views
154
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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