Hi all,
I'm finding this one a bit tricky.
I need to create unique numbers for the below info
ID. Date
349-006 02/06/14
349-006 02/06/14
349-006 02/06/14
349-006 04/06/14
349-006 04/06/14
The first 3 rows would have the ID of 1 because they share the same date but the other 2 rows would be ID of 2. And so on..
Any ideas guys?
Thanks as always
see attached
ID.xlsx
Click on the star if you think I helped you
Hi there,
if Your data are in ine column (f.e. A2:A10), than try to copy formula below to B2 and drag down:
Formula:
=IF(MATCH(A2;$A$1:A10;0)=ROWS($A$1:A2);B1+1;B1)
(B1=0)
Is this what You are looking for?
Regards
Miroslav R.
(If You like my solutions, feel free to add reputation.)
whong answer - deleted
Last edited by Miroslav R.; 06-02-2014 at 08:42 AM. Reason: deleted
Why not just use the date part as a unique number?
=VALUE(RIGHT(A1,8))
349-006 02/06/14 349-006 02/06/14 349-006 02/06/14 349-006 04/06/14 349-006 04/06/14
Last edited by AlKey; 06-02-2014 at 08:53 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi alkey
I'm using the numbers 1 to 50 instead of date ID because I'm doing a concat/vlookup to create a list of last 50 occurrences of that ID
Hi
See the file!
Regard
micope21
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
Hi micope,
Can you please paste the formula as I can't open the attachment at work .
Many many thanks for your help
Adyteo done column A if value date on column B.Originally Posted by micope21
I did the rest, really you need see the file!
There are 6 formula with 3 array formula!
I'm not 100% sure which one you want?
So I done both!! This including dropdown!!
Regard
micope21
Nickyh, did you have a look at the file I attached? Did it work?
Hi adyteo
I can't open attachments on my iPhone and my work has restricted access.
Can you paste the formula please
Couple of questions.
1. will they always be the same ID? so just need 1,2,3,4..50 or will there be different id's so will need
349-006-01, 349-006-02...?
2. Will they always be grouped together like your example or will dates be out of order, intermingled, etc?
Please be specific as to how it will look. Thanks
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Assuming worst case scenario.
Assuming data starts in A3
Use this ARRAYED function in C3 copied down
=IF(SUMPRODUCT(($A$3:A3=A3)*($B$3:$B3=B3))=1, A3&"_"&SUMPRODUCT(($F$2:$F2<>"")/COUNTIF($F$2:F2,$F$2:$F2&""))+1,INDEX($F$3:F3,MATCH(A3&B3,$A$3:$A3&$B$3:B3,0)))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Here are the formulae:
ID Value =1 349-006 02/06/14 =IF(B3=B2,A2,A2+1) 349-006 02/06/14 =IF(B4=B3,A3,A3+1) 349-006 02/06/14 =IF(B5=B4,A4,A4+1) 349-006 04/06/14 =IF(B6=B5,A5,A5+1) 349-006 04/06/14
Thanks adyteo,
But what if there are other references in between such as:
349-006 02/04/14
349-006 02/04/14
112-758 03/04/14
828 03/04/14
349-006 04/04/14
112-758 04/04/14
Lines 1 & 2 would both be number 1 coz they both appeared on the same day but line 5 would have the value of 2 because that's the second date that the ID's appeared. Same with lines 3 and 6
formula updated. is it better now?
ID.xlsx
yes, but what you can do is to sort ascending the data you have before entering the formula.
Not sure what you mean byWould 3 be a 1 and 6 be a 2?same with lines 3 and 6
Hi ChemistB
Yes they would be a 1 and a 2
Nickyh, did you have a look at the new file?
Sorry for the delay in replying adyteo,
You're almost there, the ID 112-758 with the earliest date should also have the number 1 because thats the first time the ID appeared.
The end result im after is listing the last 30 times that ID was used on unique dates.
Similar to the right hand side of this attachment
ID.xlsx
With text to column.
After that 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.
Appreciate your help oeldere but a pivot table is possibly not going to be the best solution as the end result im after is likely to feed several other reports and be single ID specific at anyone time.
I copied the date to column C.
After that text to column.
After that a formula.
After that a filter on the desired result (and exclude the empty cells in the count column).=if(countif($B$2:$B11,$B11)=countif($B$2:$B$100,$B11),countif($B$2:$B11,$B11),"")
See the attached file.
See the attached file
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks