Complex formula..

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,104
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,071
Office Version
2019
Platform
Windows
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:

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,344
Office Version
365
Platform
Windows
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.12
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
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,344
Office Version
365
Platform
Windows
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.12
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
 

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
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]
 

Forum statistics

Threads
1,081,574
Messages
5,359,704
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top