LinkBack URL
About LinkBacks
i want put name to numbers which numbers end with XXYYY example 98841XXYYY i want put name in next cell,
i have many number like this ending 98841XXYXX, AND 98841YYYXX, 98841XXXXX ,
IS THERE POSSIBLE?
i want put name to numbers which numbers end with XXYYY example 98841XXYYY i want put name in next cell,
i have many number like this ending 98841XXYXX, AND 98841YYYXX, 98841XXXXX ,
IS THERE POSSIBLE?
Formula:
Please Login or Register to view this content.
or if you have a table
Formula:Please Login or Register to view this content.
Pls find this excel , and reply me.
Last edited by vengatvj; 09-20-2013 at 05:55 AM.
maybe this
Formula:Please Login or Register to view this content.
Last edited by martindwilson; 09-19-2013 at 08:04 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Wow... Wow... Very thank you very much dear.... Thanks a lot .....
HI,
your formula is very good for me. very thanks a lot. pls find this excel for me.
Last edited by vengatvj; 09-20-2013 at 07:29 AM.
now you have changed the criteria
Hi vengatvj ..
just a side note..
they all are silver / golden actual number..
please don't update ph number with person name.. just for security reason..
I dont know.. which rule.. but its against forum rules..
@ mod : please delete.. the attachment.. I think martin has already downloaded.. will come soon with his brilliant technique..
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
yah.. ok,thank you very much
find the attached file
vengat,
I have removed your attachment as it contains sensitive data. Ensure that you re-attach the file with dummy data.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi all experts, pls find this excel and help me ,
hi nflsale, thanks for help me, i have some problem using this excel. Some number not come in
see find the attachment
in j6 of sheet "for you"
Formula:Please Login or Register to view this content.
hi nflsales, your formala is working well 100% but i have problem transfer to my original excel, here i attached my original excel , if you have free time just look up this attachment NFLSALES, thank you dear friend.
what is going on here first you ask about gold/silver now you ask about paid number
This excel for martin
rather than redo the whole thing just change all the text in the formula i gave you to "paid number"
martin, silver number and paid number doesnt matter. if all numbers come in same order means, you can create easy. so that only i changed for u. see my previous post excel, "for martin.xlsx" some numbers not working , please see this excel first sheet, i have logic number in first sheet, second sheet is my working sheet, for your refference i give all logic numbers... sorry, its last trying in this.
is this it?
many thanks to you. its working well. i thought its not possible. but done. great job, thanks a lot
MARTIN, your excel working well, but some numbers wrongly showing "paid number"
MARTIN,
your excel ok to me, working well,i attached excel, i dont know why showing like that,
pls see this excel..
ok confirm some things
all logic numbers i given in this excel.
now its morning! heres another go
martin your before one excel is good. its something error.. i have hesitate to ask you again and again. your previous excel working well. just one two numbers wrongly shows paid number. any way thank you very much dear friend.. just some wrong result i given
1113111131 PAID NUMBER
9999934567 PAID NUMBER 34567 not in logic
9999901234 PAID NUMBER 01234 not correct in this logic 12345 only correct, its not possible to add, so remove it,
9999943210 PAID NUMBER its also, correct if end 54321, but not correct if end 98765, this logic also not need
9626955826 PAID NUMBER 55826 NOT IN LOGIC
9047444704 PAID NUMBER NOT IN LOGIC
9047444714 PAID NUMBER NOT IN LOGIC
9787733397 PAID NUMBER NOT IN LOGIC
9787277331 PAID NUMBER NOT IN LOGIC
anyhow, your previous excel is almost working well, thank you very much dear friend..
Formula:Please Login or Register to view this content.
Can you guide me how this formula run? I hope i cant understand, just i will try
its just all these joined within an OR
right(i6,3)="000",
mid(i6,6,3)="000",
right(i6,3)="786",
and(mod(mid(i6,6,2),11)=0,mod(right(i6,3),111)=0),
and(mod(mid(i6,6,3),111)=0,mod(right(i6,2),11)=0),
mid(i6,6,2)=right(i6,2),
mid(i6,6,3)=right(i6,3),
mod(right(i6,4),1111)=0,
mod(mid(i6,6,4),1111)=0,
mid(i6,2,5)=right(i6,5),
mod(right(i6,5),11111)=0,
or(right(i6,5)={"12345","54321"})
Originally Posted by martindwilson
Formula:Please Login or Register to view this content.
ALL NUMBERS WORKING WELL, ONLY ONE WRONG LOGIC NUMBER IN THIS FORMULA IF YOU DONT MIND JUST REMOVE THAT,
SOME NUMBERS ENDING LIKE THIS LOGIC, ITS WRONG.
98458XTZXT THIS LOGIC WRONG
98458PTYPT THIS LOGIC WRONG
98458QTKQT THIS LOGIC WRONG
98458WOVWO THIS LOGIC WRONG
IN MY EXCEL THIS NUMBERS ONLY WRONGLY SHOW PAID NUMBER AS FOR THE ABOVE LOGIC, KINDLY REMOVE THIS LOGIC ONLY. OTHERWISE ALL WORKING WELL.
97877 46146 paid number -REMOVE THIS LOGIC
95976 10510 paid number -REMOVE THIS LOGIC
90472 81581 paid number -REMOVE THIS LOGIC
90923 63963 paid number -REMOVE THIS LOGIC
90470 58658 paid number -REMOVE THIS LOGIC
98439 61861 paid number -REMOVE THIS LOGIC
89402 67767 paid number -REMOVE THIS LOGIC
73732 41141 paid number -REMOVE THIS LOGIC
96555 25625 paid number -REMOVE THIS LOGIC
98785 55655 Its right
87358 22722 its right
Last edited by vengatvj; 09-21-2013 at 05:25 PM.
abcdeXXYXX PAID NUMBER TRUE true, example 22522, or 99599
ah it must be this bit
mid(i6,6,2)=right(i6,2)
needs to be AND(MOD(MID(I6,6,2),11)=0,MOD(RIGHT(I6,2),11)=0)
Formula:Please Login or Register to view this content.
hi time now 3.30am am in office , night shift. what is the time now?abcdeXXYXX PAID NUMBER TRUE true, example 22522, or 99599
ah it must be this bit
mid(i6,6,2)=right(i6,2)
needs to be AND(MOD(MID(I6,6,2),11)=0,MOD(RIGHT(I6,2),11)=0)
Formula:Please Login or Register to view this content.
martin,
this logic is true. 55755, 33633 , 11911, 77277, 66166,
this logic is false 66277, 33544 , 99377, 11866, 66188, remove this form your formula
and in this formula unable to show this type of numbers "EXCELEXCEL" (first 5 digit repeat ) ex "55467-55467" This logic missing
Last edited by vengatvj; 09-21-2013 at 06:11 PM.
this logic is true. 55755, 33633 , 11911, 77277, 66166,
this logic is false 66277, 33544 , 99377, 11866, 66188,
in that case
needs to be AND(MOD(MID(I6,6,2),11)=0,MOD(RIGHT(I6,2),11)=0)
needs to be
AND(MOD(MID(I6,6,2),11)=0,MOD(RIGHT(I6,2),11)=0,MID(I6,6,2)=RIGHT(I6,2))
also LEFT(I6,5)=RIGHT(I6,5) needs to be added
Formula:Please Login or Register to view this content.
Last edited by martindwilson; 09-21-2013 at 06:50 PM.
double posted again
what a brillient you are.. many functions in this formula.. i know only vlookup, true or false thats all. countless thanks to you
MARTIN, Many thanks to you for your previous help., now my office updated new things. kindly create a formula again to me,
YOUR PREVIOUS FORMULA (POST #33) VERY helped me a lot., many thanks to you my dear buddy,
In my office updated some things, which is given below.
9884444415 if any numbers comes continuously in 5 times then its consider as GOLDEN NUMBER.
AND MORE..
9811111475,
9845788888
(ALL THIS ABOVE GOLDEN NUMBERS, ANY ONE NUMBERS REPEAT 5 TIMES LIKE THIS THEN ITS CONSIDER AS GOLDEN NUMBER,(its may be comes in middle, first, or last)
AND MORE..
9884444441 if any numbers continuously in 6 times then its consider as PENTA NUMBER
(ANY 6 NUMBER CONTINUOUSLY THEN ITS PENTA NUMBER, ITS MAY BE ANYWHERE within THE 10 DIGIT MOBILE NUMBER
(ADD THE ABOVE LOGIC IN YOUR PREVIOUS FORMULA POST #33)
Thats all,
OTHER numbers logic same as your previous formula (POST #33), now I want DISPLAY that number type in next cell,
if this logic xxxyy- its silver number,
98841 xxyyy its silver number
98841 xxyxx its silver number
98841 xx786 its silver number
98841 xy786 its silver number
98841 xxxxy its golden number
98841 yyyyx its golden number
98841 00048 its silver number (000 treble zero)
98841 48000 its silver number ( last 000 treble zero)
98841 xyxyx its silver number
98841 98841 its penta number
98841 12345 its penta number
98841 54321 its penta number
reffer your previous formula (POST #33), now i need a name what type of number is that instead of display "paid number", and add some new logic as i told above,
thank you
Last edited by vengatvj; 03-22-2014 at 04:04 PM.
Martin, are you there
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks