I am looking for a formula that will return sequential numbers based on the first itteration of a combination of two ranges and then zero for the other itterations of the same combination. I've attached a sample to demonstrate.
Many thanks
I am looking for a formula that will return sequential numbers based on the first itteration of a combination of two ranges and then zero for the other itterations of the same combination. I've attached a sample to demonstrate.
Many thanks
Thanks,
HangMan
You can say "Thank you!" by clicking Add Reputation below the post.
Please, mark your thread [SOLVED] if you are happy with the solution.
=sumproduct(--($A$2:A2=A2),--($D$2:D2=D2))
Hi tim,
I'm afraid this doesn't give the expected result, it gives the next sequential number to each instance of the same column A and D combination, what I need is the same number for each combination (with the first instance showing the sequential number and other instances showing zero.
If you add this in column J and then compare it to the expected result in column I you'll see this doesn't give the correct result.
I could possibly get away witout having the zero's and just having the same number for each occurance of the same combination for columns A and D.
Last edited by HangMan; 10-31-2015 at 12:08 PM.
I think you have some errors in col I.
Row\Col A D I J K 1 Code Supplier Desired Helper Formula 2 AB123 12378 1AB123-12378 1 3 AB124 26546 1AB124-26546 1 4 AB123 54687 2AB123-54687 2 5 AB124 46390 1AB124-46390 2 6 AB127 62983 1AB127-62983 1 7 AB123 54378 3AB123-54378 3 8 AB123 12378 0AB123-12378 0 9 AB123 12378 0AB123-12378 0 10 AB131 87345 1AB131-87345 1 11 AB132 87345 1AB132-87345 1 12 AB124 26546 0AB124-26546 0 13 AB124 26546 0AB124-26546 0 14 AB133 87345 1AB133-87345 1 15 AB133 87345 0AB133-87345 0 16 AB124 26546 0AB124-26546 0 17 AB123 46390 4AB123-46390 4 18 AB136 46390 1AB136-46390 1 19 AB124 46390 0AB124-46390 0 20 AB141 87345 1AB141-87345 1 21 AB127 87345 1AB127-87345 2 22 AB124 87345 1AB124-87345 3 23 AB144 87345 1AB144-87345 1 24 AB130 54687 1AB130-54687 1 25 AB146 54378 1AB146-54378 1 26 AB147 87345 1AB147-87345 1 27 AB123 54687 0AB123-54687 0 28 AB149 54687 1AB149-54687 1 29 AB127 62983 0AB127-62983 0 30 AB124 54378 1AB124-54378 4
J2 and down: =A2 & "-" & D2
In K2, confirmed with Ctrl+Shift+Enter and copied down,
=IF(COUNTIF($J$1:K1, J2), 0, MAX(IF($A$1:$A1=A2, $K$1:K1)) + 1)
Entia non sunt multiplicanda sine necessitate
i2=IF(ROWS(K$2:K2)=1,1,IF(COUNTIFS(A$2:A2,A2,D$2:D2,D2)>1,0,MAX(INDEX((A1:A$2=A2)*I1:I$2,0))+1))try this and copy towards downPlease Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
I came up with:
H2:
I2:Please Login or Register to view this content.
with results similar to shg's and discrepancy in I5, 21,22 and 30.Please Login or Register to view this content.
What is correct?
Quang PT
@shg
Yes, you are right, there was an error, thanks for picking that up, I've corrected it in the attachment.
Thanks also to Bebo and nflsales... all formula's work, many thanks... I'm curious to understand if any one formula is more efficient than any other as I need to apply this to some 250,000 rows!
The second challenge is to make the 1st instance, i.e. the instance that returns 1 for each combination of A and D be the one where the SUM of the values for that combination in column G is the highest...
So taking AB123 as an example, the SUM of each combination is
AB123 - 12378 = £25,352.00
AB123 - 46390 = £2,639.00
AB123 - 54378 = £15,527.00
AB123 - 54687 = £28,438.00
The key is that using the same logic AB123 - 54687 now should = 1. In an ideal world and probably formulaically it would be great if it worked in order of value so that
AB123 - 12378 = 2
AB123 - 46390 = 4
AB123 - 54378 = 3
AB123 - 54687 = 1
Is that possible or asking the impossible?
Many thanks to everyone, it's really interesting to see the differing approcaches...
with a pivot table.
see the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Based on the file from your another thread, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
In K2
and copy down.Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
@ oeldere
Sadly, whilst a pivot table would give me the answer, I can't practically use one in this instance owing to the nature of the structure of the spreadsheet, but many thanks for your reply.
@sktneer
That gives the perfect answer in a formula which is exactly what I need, I now just need to break it down to understand it, many thanks...
You're welcome. Glad I could help.
Thanks for the feedback also.
No Problem, thanks for your help...
Last edited by HangMan; 11-08-2015 at 07:37 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks