need help please in contructing a macro for excel, please!

[estatefinds]

I have numerical data in the form of non repeating combinations, these combinations are made up of four numbers 0 to 9.

There are 24 set possible combinations for the non repeating combinations.
I assigned a letter for each unique combination.

now for example I use the 1 2 3 4 to show how it looks.

I need a Macro in which I select a column of data that consists of 4 number non repeating combination. when I mean non repeating it means, like 1234 not 1233 etc.

So upon selecting this column of four digit numbers non repeating it would return the Letter of the order of the combination directly to the right to the next column. so if the number selected is 7890 then the letter would be A , if it is 7980 it would be letter C.

the data in the file is to show what letters are assigned to which combination. Macro can be used in the column D.
Thank you very much in advance for the help!
Sincerely Dennis








1234

A





1243

B





1324

C





1342

D





1423

E





1432

F





2134

G





2143

H





2314

I





2341

J





2413

K





2431

L





3124

M





3142

N





3214

O





3241

P





3412

Q





3421

R





4123

S





4132

T





4213

U





4231

V





4312

W





4321

X

[Paul_Hossler]

Option Explicit
Sub test()
Dim L As String
Dim N As Long

N = 3142

L = Application.WorksheetFunction.VLookup(CStr(N), ActiveSheet.Range("A:B"), 2, False)

MsgBox L
End Sub

[estatefinds]

I ran code but just got a mesage box with letter N.

So, say I have data a column of numbers I select then click Alt F8. the macro would then result to the right of the column of the 4 number combination of non repeating numbers the letter of the order in which the numbers are dispalyed in the Cell. so any random 4 digit non repeating number will have an order such as the ones like the example in column A.
the data I Have in the column A and B is to show the template meaning the order or arrangement of number in the cell will have a letter that signifies that particular order or the the arrangement of the number.
1234 will be signified by letter A; or if there is a number 6789 is selected the macro will show that this is an A arrangement. 9768 will be the letter U for that arrangement of that number.

so say I have a column of say 50 random numbers I would select, then run a Macro that would show me in the column to the right, the arrangement of each number by a letter A through X.
Now I would be able to go back and easily read what arrangement each combination is.

[Paul_Hossler]

Select the cells in col F in the attachment and then Alt-F8 and run the macro test



Option Explicit
Sub test()
Dim r As Range

On Error Resume Next
For Each r In Selection.Cells
r.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(CStr(r.Value), ActiveSheet.Range("A:B"), 2, False)
Next
On Error GoTo 0
End Sub

[estatefinds]

ok, you have the idea!!!
I need to have it be able to do this with a column of different combinations for Example:


2657


1524


3642


4567


7584


8041


6347


8214


5234


8571


8563


7215


1534


1624


1432


5241

[Paul_Hossler]

I have no idea what you mean

You had 26 number-letter pairs originally

What does (for example) 5241 get mapped to?

[estatefinds]

ok, I have 24 combinations as there are only 24 possible combinations for a given non repeating combination for example 1234, the letter is just an identifier of the arrangement of the of the 24 unique arrangements of the combinations.

so the 5241 gets the letter V.

So ultimately The macro based on The example will work on any data as long as it uses the numbers 0 to 9,

and the combinations of the number is a non repeating combination meaning for example: 5247, as you see there are no repeating numbers within the cell. so the macro would recognize any number and what unique arrangement of the combination will be, so it would result the letter to the right corresponding to the arrangement it is in.
the example below shows where the 5241 gets the letter V


1245
A


1254
B


1425
C


1452
D


1524
E


1542
F


2145
G


2154
H


2415
I


2451
J


2514
K


2541
L


4125
M


4152
N


4215
O


4251
P


4512
Q


4521
R


5124
S


5142
T


5214
U


5241
V


5412
W


5421
X


another example below to show consistancy



2478
A


2487
B


2748
C


2784
D


2847
E


2874
F


4278
G


4287
H


4728
I


4782
J


4827
K


4872
L


7248
M


7284
N


7428
O


7482
P


7824
Q


7842
R


8247
S


8274
T


8427
U


8472
V


8724
W


8742
X

[Paul_Hossler]

The first group of 25 worked when they were in columns A:B with this formula



r.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(CStr(r.Value), ActiveSheet.Range("A:B"), 2, False)


The second group of 25 should work when they are in columns C : D with this formula



r.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(CStr(r.Value), ActiveSheet.Range("C:D"), 2, False)

[estatefinds]

so, is there a way of having this macro run in which say how the


Option Explicit
Sub test()
Dim r As Range

On Error Resume Next
For Each r In Selection.Cells
r.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(CStr(r.Value), ActiveSheet.Range("A:B"), 2, False)
Next
On Error GoTo 0
End Sub

runs, but have it so what ever number from the range of 1234 to 7890 in which ever arrangement is in the column, it will return the letter of the arrangement of those numbers?

[Paul_Hossler]

For each cell that is selected

1. the macro will look in Col A for the number
2. get the letter in Col B on the same row as the number
3. put the letter next to the cell in the selection,
4. get the next selected cell
5. repeat

It doesn't matter how long col A is

[Aussiebear]

Absolutely. Create a master table with the designations. Then use a Vlookup function offset value to return the alphabetical value for the cell value.

Paul indicated whilst I was posting. ( but then he's always quicker)

[Paul_Hossler]

Paul indicated whilst I was posting. ( but then he's always quicker)




Actually, I'm still struggling with trying to figure out what the requirement really is

[estatefinds]

No problem, so just need to identify the arrangement of a four number non repeating combination wether it is in the order of smallest to greatest 1234 3789. 7890. 5678 which would be identified by the letter A. Or the other arrangements B through X.

[Paul_Hossler]

No problem, so just need to identify the arrangement of a four number non repeating combination wether it is in the order of smallest to greatest 1234 3789. 7890. 5678 which would be identified by the letter A. Or the other arrangements B through X.

ANY one of the VLookup macros suggested will find a number in a column and return the letter associated with it

Why is 5678 identified with the letter A?

There must be a range with at least 2 columns that has 5678 and A on the same row

What does your data look like?

[estatefinds]

Because it goes from smallest to largest from left to right.
The data in column F would look like this:
4312
7689
2235
this column will be over 500 combinations some will repeat in the column.

[estatefinds]

working on that data to help list all the combinations. ill get back to you on this Thank you:)

[estatefinds]

ok here is the file.

I Have numerical data that are non repeating combinations Meaning; 1234 not 1223 in the Column AA, and in Column AB is the letter that coresponds to the arrangement of the at combination in Column AA. This master list is a set list. This is what the macro would be using in the back ground.
So for example I enter data in column AC as seen in example then I run the macro, and it returns the letter of the arrangement of that number I just entered. every time I enter a number I run the macro and it will record the letter in Column AD.

Thank you very much in advance for your help on this!!!:)

[Paul_Hossler]

Option Explicit

Sub test3()
Dim rAC As Range, rAAAB As Range, r As Range

Set rAC = ActiveSheet.Range("AC:AC").SpecialCells(xlCellTypeConstants)
Set rAAAB = ActiveSheet.Range("AA:AB")


On Error Resume Next
For Each r In rAC.Cells
r.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(r.Value, rAAAB, 2, False)
Next
On Error GoTo 0
End Sub

[estatefinds]

HI, do I select anything then run code? I ran it but nothing happened

[Paul_Hossler]

I tested it doing what you said




I enter data in column AC as seen in example then I run the macro, and it returns the letter of the arrangement of that number I just entered. every time I enter a number I run the macro and it will record the letter in Column AD.



Put numbers in col AC, and run the macro

It looks up the numbers from AC in col AA, and puts the matching letter from AB beside the number in AD

[estatefinds]

ok I see what happend the data needs to be entered at the row 42 I just rean it and it works!!! thank you!!!

[Paul_Hossler]

Actually, it should work anywhere

I noticed that some of the numbers in AA and AC were really text, although they looked like numbers

This version might be a little more robust since it makes AA:AD text for consistancy



Option Explicit

Sub test3()
Dim rAC As Range, rAAAB As Range, r As Range

Set rAC = ActiveSheet.Range("AC:AC").SpecialCells(xlCellTypeConstants)
Set rAAAB = ActiveSheet.Range("AA:AB")

ActiveSheet.Range("AA:AD").NumberFormat = "@"

On Error Resume Next
For Each r In rAC.Cells
r.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(CStr(r.Value), rAAAB, 2, False)
Next
On Error GoTo 0
End Sub

[estatefinds]

Thank you very much!!!!!:)