Complex formula..

[VBABEGINER]

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..

 

[alansidman]

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.

 

[AhoyNC]

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
	A	B	C	D
1
2	Admin Project 1	11.2		Admin Project 1
3	Admin Project 2	10.12		11.2
4	Admin Project 1	5.32		5.32
5	Admin Project 1	45.21		45.21
6	Admin Project 2	19.10
7	Admin Project 1
8	Admin Project 3
9	Admin Project 2
10	Admin Project 3
11	Admin Project 1
Sheet

 

[VBABEGINER]

Excellent..

 

[AhoyNC]

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
	A	B	C	D
1
2	Admin Project 1	11.2		Admin Project 1
3	Admin Project 2	10.12		11.2
4	Admin Project 1	5.32		5.32
5	Admin Project 1	45.21		45.21
6	Admin Project 2	19.1
7	Admin Project 1
8	Admin Project 3
9	Admin Project 2
10	Admin Project 3
11	Admin Project 1
Sheet

 

[AlKey]

Try this regular formula
Enter in D3 and copy down

[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]