Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
Do you still need an answer on this?
This will probably need a coding solution. Are there only two rows in each set? "Andrea" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
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" wrote: Do you still need an answer on this? This will probably need a coding solution. Are there only two rows in each set? "Andrea" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
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" wrote: 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" wrote: Do you still need an answer on this? This will probably need a coding solution. Are there only two rows in each set? "Andrea" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
My first row is
A B C Row 1 - 10060 CC 113 Row 2 - 10060 TS 185 "Sheeloo" wrote: 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" wrote: 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" wrote: Do you still need an answer on this? This will probably need a coding solution. Are there only two rows in each set? "Andrea" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
My first row is
Row 1 - 10060 CC 113 Row 2 - 10060 TS 185 "Sheeloo" wrote: 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" wrote: 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" wrote: Do you still need an answer on this? This will probably need a coding solution. Are there only two rows in each set? "Andrea" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
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... -- If you find this post helpful pl. choose "Yes"... "Andrea" wrote: My first row is Row 1 - 10060 CC 113 Row 2 - 10060 TS 185 "Sheeloo" wrote: 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" wrote: 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" wrote: Do you still need an answer on this? This will probably need a coding solution. Are there only two rows in each set? "Andrea" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
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))) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
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" wrote: 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))) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with logic or Vlookup formula
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))) -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Andrea" wrote: 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" wrote: 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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the color of a cell in a logic formula | Excel Discussion (Misc queries) | |||
Logic Formula | Excel Worksheet Functions | |||
Array Formula Using Max Match Logic | Excel Discussion (Misc queries) | |||
Logic formula using Time | Excel Discussion (Misc queries) | |||
Need help setting up a formula using perhaps the logic functions . | Excel Worksheet Functions |