| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add a number '1' next to highest number in column
Column A has numbers in it and some of these match.
Column B has numbers in it and some of these match. The columns are already sorted by A then B. What I need to do is where a number matches in column A, I need a number €˜1 inserting in column C next to the highest number in column B. This needs to be copied down the sheet. A B C 1 1 5 1 2 1 2 3 1 1 4 2 3 5 3 2 1 6 3 2 7 3 2 8 4 3 1 9 4 2 10 4 2 11 4 2 12 4 1 As you can see in the example above if there is more than one number the same in column B then only one €˜1 needs to be inserted. If there is only one unique number in column A then nothing needs to be inserted in column C. Thanks in advance for any help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add a number '1' next to highest number in column
Try this formula in C1...
=IF(ROW()=SUMPRODUCT(--($A$1:$A$100=A1),--($B$1:$B$100=MAX($B$1:$B$100)),--ROW($B$1:$B$100)),1,"") If this post helps click Yes --------------- Jacob Skaria "Mally" wrote: Column A has numbers in it and some of these match. Column B has numbers in it and some of these match. The columns are already sorted by A then B. What I need to do is where a number matches in column A, I need a number €˜1 inserting in column C next to the highest number in column B. This needs to be copied down the sheet. A B C 1 1 5 1 2 1 2 3 1 1 4 2 3 5 3 2 1 6 3 2 7 3 2 8 4 3 1 9 4 2 10 4 2 11 4 2 12 4 1 As you can see in the example above if there is more than one number the same in column B then only one €˜1 needs to be inserted. If there is only one unique number in column A then nothing needs to be inserted in column C. Thanks in advance for any help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add a number '1' next to highest number in column
Oops....the below formula wont work...
If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try this formula in C1... =IF(ROW()=SUMPRODUCT(--($A$1:$A$100=A1),--($B$1:$B$100=MAX($B$1:$B$100)),--ROW($B$1:$B$100)),1,"") If this post helps click Yes --------------- Jacob Skaria "Mally" wrote: Column A has numbers in it and some of these match. Column B has numbers in it and some of these match. The columns are already sorted by A then B. What I need to do is where a number matches in column A, I need a number €˜1 inserting in column C next to the highest number in column B. This needs to be copied down the sheet. A B C 1 1 5 1 2 1 2 3 1 1 4 2 3 5 3 2 1 6 3 2 7 3 2 8 4 3 1 9 4 2 10 4 2 11 4 2 12 4 1 As you can see in the example above if there is more than one number the same in column B then only one €˜1 needs to be inserted. If there is only one unique number in column A then nothing needs to be inserted in column C. Thanks in advance for any help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add a number '1' next to highest number in column
Thanks for your help Jacob.
I got sorted by adding a few more columns and doing a sort "Jacob Skaria" wrote: Oops....the below formula wont work... If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try this formula in C1... =IF(ROW()=SUMPRODUCT(--($A$1:$A$100=A1),--($B$1:$B$100=MAX($B$1:$B$100)),--ROW($B$1:$B$100)),1,"") If this post helps click Yes --------------- Jacob Skaria "Mally" wrote: Column A has numbers in it and some of these match. Column B has numbers in it and some of these match. The columns are already sorted by A then B. What I need to do is where a number matches in column A, I need a number €˜1 inserting in column C next to the highest number in column B. This needs to be copied down the sheet. A B C 1 1 5 1 2 1 2 3 1 1 4 2 3 5 3 2 1 6 3 2 7 3 2 8 4 3 1 9 4 2 10 4 2 11 4 2 12 4 1 As you can see in the example above if there is more than one number the same in column B then only one €˜1 needs to be inserted. If there is only one unique number in column A then nothing needs to be inserted in column C. Thanks in advance for any help. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add a number '1' next to highest number in column
Fine Mally..
However the below formula will assign 1 aganist the maximum value... =IF(B1=SUMPRODUCT(MAX(($A$1:$A$100=A1)*($B$1:$B$10 0))),1,"") If this post helps click Yes --------------- Jacob Skaria "Mally" wrote: Thanks for your help Jacob. I got sorted by adding a few more columns and doing a sort "Jacob Skaria" wrote: Oops....the below formula wont work... If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try this formula in C1... =IF(ROW()=SUMPRODUCT(--($A$1:$A$100=A1),--($B$1:$B$100=MAX($B$1:$B$100)),--ROW($B$1:$B$100)),1,"") If this post helps click Yes --------------- Jacob Skaria "Mally" wrote: Column A has numbers in it and some of these match. Column B has numbers in it and some of these match. The columns are already sorted by A then B. What I need to do is where a number matches in column A, I need a number €˜1 inserting in column C next to the highest number in column B. This needs to be copied down the sheet. A B C 1 1 5 1 2 1 2 3 1 1 4 2 3 5 3 2 1 6 3 2 7 3 2 8 4 3 1 9 4 2 10 4 2 11 4 2 12 4 1 As you can see in the example above if there is more than one number the same in column B then only one €˜1 needs to be inserted. If there is only one unique number in column A then nothing needs to be inserted in column C. Thanks in advance for any help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return rating of which number is highest and descending in column | Excel Worksheet Functions | |||
| 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
| Adding Column and removing highest number | Excel Discussion (Misc queries) | |||
| Determining the highest number in a row | Excel Discussion (Misc queries) | |||
| Determining the highest number in a row | Excel Discussion (Misc queries) | |||
Linear Mode
