function to compare and shift compared cell that has a bigger number

[naruturt]

1234	1	234	2	34	3
132	1	132	1	132	1
4231	4	4231	4	4231	4
2	2	2	2	2	2

guys, i am a newbie trying to figure out how to do this problem in excel. column one contain a range of numbers, each of these numbers consist of numbers 1 through 4. i want to take the left most digit of all four numbers in the first column, find duplicates, then shift the longest one with duplicate indication left by 1 digit(effectively removing the left most digit). for example, number 1234 and 132, left most digit of both is 1, i want to shift 1234 left one digit, that will result in column 3 as shown above. then repeat same process with the result of column 3. etc.
i don't need to see the process, but i do need to see the final result. the result for problem above is 3142.
would you please help me write a function that i can call and input range of numbers to get a result.

thanks for your help!

Naru

[6StringJazzer]

Welcome to the Forum!

Your algorithm seems to assume that only one digit can repeat as the first digit in any given column of numbers. I have made the same assumption in this solution. You will notice that the process repeats three times, because you may have to remove up to three digits, even though you may reach the solution in fewer than three iterations. Nonetheless, the correct final answer appears in the final column.

[Jacc]

a bit late but since I already made it...

[naruturt]

6StringJazzer,

thanks for your post, i see how you do it with formulas... are you familiar with macro/vba enough to create me a code / function i can call and then select the range of 4 numbers to do that same process and only give me the final result in the cell that i call that function? for example: =Solution(SelectedRange), when i hit enter i can see the result populated in the same cell i type.

thanks,

Naru

[naruturt]

Jacc,

thanks for your solution, i need a function/vba code that i can type in 1 cell and get the result in that same cell after i hit enter. are you familiar with macro/VBA enough to help me with this problem? thanks in advance for your help! i've been trying for the whole day today. i am just awful at vba, need to take some courses...LOL.

thanks,

Naru

[naruturt]

Guys,

thanks for your replies, both of you gave me a good solution. But i need to do it over many sets of 4 numbers, if i can call a function and select a range of 4 numbers and get result. that would be great. it would proof to be a big headache to do it only using formulas and using multiple cells for intermediate steps.... not to mention the size of the excel file would be HUGE....
i am stuck since i don't know VBA. Can someone please help!

thanks,

Naru

[6StringJazzer]

Here is a function to perform this. It requires a single contiguous range containing 4 integers in the same column. The integers must have four digits or fewer. The allowable digits are 1-4.

It could be modified to accept four separate numbers instead of a single range, if your numbers are not arranged in a single column as shown in your example.

For valid cases it returns a 4-digit Long.

There are six error cases:

#NOT4! The range does not contain exactly four numbers
#MULTICOL! The range is not confined to one column
#TOOBIG! One of the number is more than four digits
#>4! a number contains a digit greater than 4. This error will not occur if the problem can be solved without reaching that digit as a first digit of a number.
#NOTNUM! One element in the range in not a number
#NOTINT! One element in the range is not an integer

[naruturt]

6StringJazzer,

The function you created works great. there're 2 things that need to be corrected to fit my situation and I hope it's not much trouble for you to do so...
1. the values in my cells are strings instead of numbers.
2. if there's an empty cell in the range of 4 i selected, it give an error instead of taking the values i do have and do the procedure. i'd like this procedure to give result if there is even 1 cell in the selected range have data.

sorry for the added trouble and thanks a lot for getting this done so quick. it's amazing! how long did it take you to learn VBA to be so good at it?
i am starting to learn, but it seems so difficult.

thanks,

Naru

[6StringJazzer]

I don't mind spending a little extra time on this, but I don't want to go into multiple cycles of finding out what else needs to be added next time.

How many cells can be in the range? Is it really 4? It's not hard to ignore empty cells but I want to make sure we've got the shape of the range right to start with.

What do you mean strings instead of numbers? How are the numbers entered into your worksheet? Are they formatted as text, or entered as

'1234

It would help if you showed me an actual file with some actual data.

(I have a computer science degree and 10 years of development experience followed by another 23 years of technical management experience. I learned VBA when I was taking a graduate course in forecasting in 2005 and we used Excel with VBA to do all our analysis. Learning VBA is not that hard but learning how to design and write good software is a process of lifetime learning )

[naruturt]

I don't mind spending a little extra time on this, but I don't want to go into multiple cycles of finding out what else needs to be added next time.

How many cells can be in the range? Is it really 4? It's not hard to ignore empty cells but I want to make sure we've got the shape of the range right to start with.

What do you mean strings instead of numbers? How are the numbers entered into your worksheet? Are they formatted as text, or entered as

'1234

It would help if you showed me an actual file with some actual data.

(I have a computer science degree and 10 years of development experience followed by another 23 years of technical management experience. I learned VBA when I was taking a graduate course in forecasting in 2005 and we used Excel with VBA to do all our analysis. Learning VBA is not that hard but learning how to design and write good software is a process of lifetime learning )

6StringJazzer,

Thank you so much for your time, i really appreciate the help! your code is REALLY GOOD. i was able to modify what i was doing and added 2 lines in your code to get exactly what i wanted. turned my range of string to values, then make empties as 0s, and then used your function with 2 lines added in your code to strip the result of 0s.
Once again, thanks so much for the help!

Naru