Need help with logic or Vlookup formula

[Andrea]

I am relatively new to excel and need help with creating a formula.
Based on the table below my goal is to take information from column A (or a
range of cells), and return a min from column C and a max from column D.

For code 10060, I need the initials in column B to return when the min in
column C has been located (for each code in column A) and the min amount

For the same code 10060 I need the initials in column B to return when the
max in column D has been located (for each code in column A) and the max
amount

The results for code 10060 should read
10060 cc 113 185


I.e.

A B C D
10060 CC 113 164
TS 156 185
10180 CC 327 275
TS 327 327
11041 CC 79 79
TS 60 106
11042 CC 106 106
TS 122 149
11043 CC 357 357
TS 424 424
13133 PS 50 50
TS 247 248

Thanks,
Andrea

 

[Sheeloo]

Do you still need an answer on this?

This will probably need a coding solution.
Are there only two rows in each set?

 

[Andrea]

Yes, I still need help witht his issue.
Unfortunately there is not a set number of rows that will correspond to the
data in cloumn a. There are times when there is a minumum of one row up to
unlimited rows.
Thanks,

 

[Sheeloo]

I need some clarifications;
Is your first row 10060 CC 113 164 TS 156 185

or only 10060 CC 113 164
and row 2 contains TS 156 185 in Col B, C, and D?

Also do you want
10060 cc 113 185 in one row
or like this in two rows
Row 1 - 10060 CC 113
Row 2 - 10060 TS 185

Also can you arrange your data like this
Col A - numbers (like 10060) for which you need to find Min and Max
Col B - Initials
Col C and D - Amounts

Then I can try to give you this in another sheet...
Number Initial with the Min amount (in Col C) for all rows with the number
in Col A, Min amount in D, Initial having Max amount in E, Max amount in F

 

[Andrea]

My first row is
A B C

Row 1 - 10060 CC 113
Row 2 - 10060 TS 185

 

[Andrea]

My first row is

Row 1 - 10060 CC 113
Row 2 - 10060 TS 185

 

[Sheeloo]

Andrea,

Sorry for the delay. Here you go;
Assuming you have
Code in Col A
Initial in Col B
Values in Col C
Then do this
1.
Enter headings in Col A
Select Col A
Choose Data|Filter|Advanced Filter
Select Copy to another Location
List Range should have Col A
Criteria BLANK
Enter D1 in Copy to
Click on Unique records only
Press OK
This will give you unique list of Codes
2. In E2 copy
=MAX(IF($A$2:$A$100=D2,$C$2:$C$100))
and press CTRL-SHIFT-ENTER
This will give you the MAX
[change $100, in all formulae, to the $rowno of last row in your data set]
3. In F2 copy
=MIN(IF($A$2:$A$100=D2,$C$2:$C$100))
and press CTRL-SHIFT-ENTER
This will give the MIN
4. In G2 copy
=INDIRECT("B" &
(MATCH(MAX(IF($A$2:$A$100=D2,$C$2:$C$100)),IF($A$2:$A$100=D2,$C$2:$C$100),1)))
and press ENTER
This will give you the Initial against the MAX
5. Select E2-G2 and copy down till end of Codes in Col D

You should be getting what you want...

If your MIN and MAX are in different Cols then change the ref. for MAX col
to MIN col...

 

[Sheeloo]

Step 1
I meant headings in ROW 1
Step 4
Could be simplified to
=INDIRECT("B" & (MATCH(E2,IF($A$2:$A$100=D2,$C$2:$C$100),1)))

 

[Andrea]

I posted no not helpful in error.
I have been able to get the min and max as indicated in the formulas but the
corresponding initials being correctly relayed.
Can you help with this?

 

[Sheeloo]

Can you send the file to me?

If not then did you try with
=INDIRECT("B"
(MATCH(MAX(IF($A$2:$A$100=D2,$C$2:$C$100)),IF($A$2:$A$100=D2,$C$2:$C$100),1)))