Is there a way to automatically generate calculations to adjacent cell without dragging the functions?

X

xaikus506

New Member
Joined
Oct 29, 2022
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Hello, I would like to ask if there is a way to generate calculations next to adjacent arrays such as Unique function, Filter function without automatically without dragging down functions in excel or without utilizing tables or preferably without using offset function or vba?

1667434171301.png


For example in this image the number of Job Titles can Decrease or increase and I want generate a countifs without spilling the entire formula to the entire column. Is this possible? Thank you for your help!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Book1
BCDE
1
2List of Job TitlesCountData
3Finance Coordinator5Finance Coordinator
4Project Manager5Project Manager
5Senior Advisor7Senior Advisor
6New position1Finance Coordinator
7Project Manager
8Senior Advisor
9Finance Coordinator
10Project Manager
11Senior Advisor
12Finance Coordinator
13Project Manager
14Senior Advisor
15Finance Coordinator
16Project Manager
17Senior Advisor
18Senior Advisor
19Senior Advisor
20New position
21
Sheet1
Cell Formulas
RangeFormula
B3:B6B3=UNIQUE(FILTER(E3:E1000,E3:E1000<>"",""))
C3:C6C3=COUNTIF(E3:E1000,B3#)
Dynamic array formulas.
 
Upvote 0
Solution
kevin9999 said:
Book1
BCDE
1
2List of Job TitlesCountData
3Finance Coordinator5Finance Coordinator
4Project Manager5Project Manager
5Senior Advisor7Senior Advisor
6New position1Finance Coordinator
7Project Manager
8Senior Advisor
9Finance Coordinator
10Project Manager
11Senior Advisor
12Finance Coordinator
13Project Manager
14Senior Advisor
15Finance Coordinator
16Project Manager
17Senior Advisor
18Senior Advisor
19Senior Advisor
20New position
21
Sheet1
Cell Formulas
RangeFormula
B3:B6B3=UNIQUE(FILTER(E3:E1000,E3:E1000<>"",""))
C3:C6C3=COUNTIF(E3:E1000,B3#)
Dynamic array formulas.
Click to expand...
Hi kevin9999, Thank you so much for the answer it works!
 
Upvote 0
Hi!, I would like to ask what if the adjacent cell is not an array but a raw data is there a possible way to generate something like it?
 
Upvote 0
If you mean what if cellB3 wasn't an array/spill formula but just the start of a list of manually entered data? If so then, not as far as I know. The best you could do (and I'm happy t5o be corrected) would be something like this:

Book1
BCDE
1
2List of Job TitlesCountData
3Finance Coordinator5Finance Coordinator
4Project Manager5Project Manager
5Senior Advisor7Senior Advisor
6New position1Finance Coordinator
7 Project Manager
8 Senior Advisor
9 Finance Coordinator
10 Project Manager
11 Senior Advisor
12 Finance Coordinator
13 Project Manager
14 Senior Advisor
15 Finance Coordinator
16 Project Manager
17 Senior Advisor
18 Senior Advisor
19 Senior Advisor
20 New position
21
Sheet1
Cell Formulas
RangeFormula
C3:C20C3=IF(B3<>"",COUNTIF(E3:E1000,B3),"")
 
Upvote 0
A couple of options, depending on what functions you have available.

22 11 03.xlsm
BCDE
1
2List of Job TitlesCountCountData
3Finance Coordinator55Finance Coordinator
4Project Manager55Project Manager
5Senior Advisor77Senior Advisor
6New position11Finance Coordinator
7Project Manager
8Senior Advisor
9Finance Coordinator
10Project Manager
11Senior Advisor
12Finance Coordinator
13Project Manager
14Senior Advisor
15Finance Coordinator
16Project Manager
17Senior Advisor
18Senior Advisor
19Senior Advisor
20New position
21
Sheet2 (2)
Cell Formulas
RangeFormula
C3:C6C3=TAKE(COUNTIF(E3:E1000,B3:B1000),COUNTA(B3:B1000))
D3:D6D3=INDEX(COUNTIF(E3:E1000,B3:B1000),SEQUENCE(COUNTA(B3:B1000)))
Dynamic array formulas.
 
Upvote 0
kevin9999 said:
Book1
BCDE
1
2List of Job TitlesCountData
3Finance Coordinator5Finance Coordinator
4Project Manager5Project Manager
5Senior Advisor7Senior Advisor
6New position1Finance Coordinator
7Project Manager
8Senior Advisor
9Finance Coordinator
10Project Manager
11Senior Advisor
12Finance Coordinator
13Project Manager
14Senior Advisor
15Finance Coordinator
16Project Manager
17Senior Advisor
18Senior Advisor
19Senior Advisor
20New position
21
Sheet1
Cell Formulas
RangeFormula
B3:B6B3=UNIQUE(FILTER(E3:E1000,E3:E1000<>"",""))
C3:C6C3=COUNTIF(E3:E1000,B3#)
Dynamic array formulas.
Click to expand...
You can "combine" those two formulas into a single formula (placed in cell B3) that will generate both columns of data...
Excel Formula: 
=LET(e,E3:E1000,u,UNIQUE(FILTER(e,e<>"","")),HSTACK(u,MAP(u,LAMBDA(x,COUNTIF(E3:E1000,x)))))
 
Upvote 0
Peter_SSs said:
A couple of options, depending on what functions you have available.

22 11 03.xlsm
BCDE
1
2List of Job TitlesCountCountData
3Finance Coordinator55Finance Coordinator
4Project Manager55Project Manager
5Senior Advisor77Senior Advisor
6New position11Finance Coordinator
7Project Manager
8Senior Advisor
9Finance Coordinator
10Project Manager
11Senior Advisor
12Finance Coordinator
13Project Manager
14Senior Advisor
15Finance Coordinator
16Project Manager
17Senior Advisor
18Senior Advisor
19Senior Advisor
20New position
21
Sheet2 (2)
Cell Formulas
RangeFormula
C3:C6C3=TAKE(COUNTIF(E3:E1000,B3:B1000),COUNTA(B3:B1000))
D3:D6D3=INDEX(COUNTIF(E3:E1000,B3:B1000),SEQUENCE(COUNTA(B3:B1000)))
Dynamic array formulas.
Click to expand...
Hi Peter, Thanks for the response, I am trying your formula with the function but in combination of formulas such as =TAKE(TRIM(CONCATENATE(C5,","," ",A5," ",B5)),COUNTA(C:C)-3) and =INDEX(NUMBERVALUE(TRIM(D5)),SEQUENCE(COUNTA(D:D)-3)). I have a trouble making it work. the Take function with the CountA does not return an array and the Index and Sequence function returns an array the 1st entry is correct but the cells below generate a Ref error
 
Upvote 0
kevin9999 said:
If you mean what if cellB3 wasn't an array/spill formula but just the start of a list of manually entered data? If so then, not as far as I know. The best you could do (and I'm happy t5o be corrected) would be something like this:

Book1
BCDE
1
2List of Job TitlesCountData
3Finance Coordinator5Finance Coordinator
4Project Manager5Project Manager
5Senior Advisor7Senior Advisor
6New position1Finance Coordinator
7 Project Manager
8 Senior Advisor
9 Finance Coordinator
10 Project Manager
11 Senior Advisor
12 Finance Coordinator
13 Project Manager
14 Senior Advisor
15 Finance Coordinator
16 Project Manager
17 Senior Advisor
18 Senior Advisor
19 Senior Advisor
20 New position
21
Sheet1
Cell Formulas
RangeFormula
C3:C20C3=IF(B3<>"",COUNTIF(E3:E1000,B3),"")
Click to expand...
Hi Kevin the formula on the earlier post work if the adjacent cell is an array based formula. but I had a problem making it work if my reference is a raw data that is not an array. the "#" doesnt seem to work anymore
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
138
SunnyAlv
SunnyAlv
J
  • Question
Aligning data from Text and Adjacent Cells into columns - Best way to achieve this?
Replies
4
Views
683
Peter_SSs
Peter_SSs
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
397
AndyEd
A
T
  • Solved
Filter a range and then partially concatenate results.
2
Replies
10
Views
3K
Fluff
Fluff
P
  • Solved
Seeking a better way to CountIF with dynamic range without dragging cells down columns.
2
Replies
11
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,924
Messages
6,122,294
Members
449,077
Latest member
Rkmenon

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