Complex formula..

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,232
Hi Team,
I need one formula..Condition are like below..

In Col A,
A2 Admin Project 1
A3 Admin Project 2
A4 Admin Project 1
A5 Admin Project 1
A6 Admin Project 2
A7 Admin Project 1
A8 Admin Project 3
A9 Admin Project 2
A10 Admin Project 3
A11 Admin Project 1

like this..
Against this I have in Col B,
B2 11.2
B3 10.12
B4 5.32
B5 45.21
B6 19.10
.
.
.
and like this..

I want output like this..
Admin Project 1
11.2
5.32
45.21


Admin Project 2
10.12
19.10

Can any one please guide me...this is something complex level formula..
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
On a second sheet, Copy each of your project names and paste them in Row 1 across, so that they represent headers for each column.
In your first sheet, apply the filter to row 1 (where your current headers should exist.)
Filter on each project and copy the results of column B and paste special into the appropriate column in the second sheet.

The Filter is on the Data Tab on the Ribbon. If you truly require this as a Macro, then use the macro recorder and then post your result to this forum and ask to have it converted to a loop or an array.
 
Last edited:
Upvote 0
If you need a formula then try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.
Excel Workbook
ABCD
1
2Admin Project 111.2Admin Project 1
3Admin Project 210.1211.2
4Admin Project 15.325.32
5Admin Project 145.2145.21
6Admin Project 219.10
7Admin Project 1
8Admin Project 3
9Admin Project 2
10Admin Project 3
11Admin Project 1
Sheet
 
Upvote 0
You're welcome. If you have Excel 2016 you could also use the AGGREGATE function that doesn't require CTRL-SHIFT-ENTER. Just ENTER.
Excel Workbook
ABCD
1
2Admin Project 111.2Admin Project 1
3Admin Project 210.1211.2
4Admin Project 15.325.32
5Admin Project 145.2145.21
6Admin Project 219.1
7Admin Project 1
8Admin Project 3
9Admin Project 2
10Admin Project 3
11Admin Project 1
Sheet
 
Upvote 0
Try this regular formula
Enter in D3 and copy down
Code:
[B]=IFERROR(1/(1/INDEX(B:B,SMALL(INDEX(($A$2:$A$11<>D$2)*10^10+ROW($B$2:$B$11),0),ROWS(D$2:$D2)))),"")[/B]
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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