(vba) replacing values.

[montecarlo2012]

Hello, I am not pretend to be a programmer I just try tospeak your vba language, so maybe I help a little more.


For something
If (O.value = array A:F.values)
Then H:M =P:U.values
Next
Ubound
End
A2:F2 corresponding to P values, A3:F3 to Q, and so on.
In the spreadsheet you can see on H1 value 1 because A2=9and O11=9 then replace for P.value next to O11.
Where is my array? B2:G2060
And the pu.values at P2:AMA1000
DISPLAY RESULTS AT (“i:N”)

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	9	12	15	21	33	45		1	1	4	1	0	1		1	2	2	2	2	2	2
3	20	25	29	30	36	37		2	2	0	0	3	4		2	3	3	2	2	2	2
4	2	11	13	20	27	28									3	3	3	3	4	4	5
5	4	6	15	24	47	52									4	2	2	2	1	1	0
6	10	16	21	25	37	49									5	1	1	1	1	1	0
7	4	5	8	13	27	44									6	1	2	2	1	1	2
8	2	3	15	39	44	49									7	1	1	1	1	1	1
9	19	23	28	38	48	53									8	3	3	3	3	3	2
10	3	8	16	20	43	48									9	1	1	1	2	2	2
11	13	16	17	40	42	44									10	2	2	3	3	2	2
12	1	12	22	44	46	52									11	1	1	1	2	3	3
13	10	13	35	36	37	42									12	1	1	2	2	2	2
14	9	26	34	40	42	49									13	4	4	3	3	3	2
15	1	2	25	43	48	53									14	1	2	2	2	2	2
16	8	32	35	46	47	52									15	4	4	4	3	3	3
17	14	15	23	36	49	50									16	4	4	4	4	3	3
18	15	17	22	40	41	45									17	2	2	3	3	3	3
19	3	26	36	37	43	44									18	0	0	0	0	0	0
20	7	16	31	38	39	42									19	1	1	1	1	1	1
21	6	14	37	41	48	49									20	3	2	1	1	1	1
22	10	11	12	17	28	46									21	1	1	1	2	1	1
23	3	9	11	21	24	49									22	2	2	2	2	2	2
24	11	24	36	45	46	47									23	2	2	2	2	2	2
25	3	6	36	46	48	52									24	1	1	1	1	2	2
26	2	7	11	15	43	45									25	3	2	2	2	1	1
27	3	6	16	30	31	40									26	2	2	2	2	2	2
28	20	44	46	48	52	53									27	2	2	1	1	1	0
29	29	33	46	48	49	53									28	2	2	2	2	2	2
30	3	4	5	39	47	48									29	1	0	0	0	0	0
31	4	8	17	20	51	52									30	1	0	0	0	0	0
32	6	10	18	27	28	51									31	1	1	1	1	1	1
33	4	8	15	16	32	52									32	1	1	1	1	1	1
34	8	21	22	27	40	44									33	0	0	0	0	0	0
35	1	3	4	12	15	38									34	1	1	1	1	1	1
36	2	10	11	41	42	53									35	2	2	2	2	2	2
37	13	17	20	23	24	30									36	4	3	3	3	4	5
38	1	7	35	39	43	47									37	4	4	4	4	3	3
39	3	13	42	47	52	53									38	2	2	2	2	2	2
40	1	3	5	14	28	32									39	2	2	2	2	2	2
41	5	13	18	28	31	43									40	3	3	3	3	3	3
42	2	7	31	40	46	52									41	1	2	2	2	2	2
43	5	8	9	12	23	27									42	4	4	4	4	4	4
44	3	16	19	20	32	52									43	3	3	3	3	3	3
45	2	29	35	37	41	42									44	5	5	5	5	5	4
46	21	24	34	41	47	52									45	1	1	1	1	2	2
47	13	19	22	44	46	52									46	2	2	3	3	4	5
48	5	15	29	35	40	47									47	2	2	2	1	2	2
49	4	36	42	47	48	52									48	3	4	4	4	4	5
50	1	21	26	30	46	48									49	4	5	5	6	5	5
51	21	24	33	37	43	46									50	1	1	1	1	1	1
52	1	11	19	33	37	49									51	0	0	0	0	0	0
53	5	6	13	33	35	40									52	3	3	3	2	2	3
54	20	23	26	32	41	46									53	2	2	2	2	2	2

thanks.

 

[Rick Rothstein]

here you are: https://app.box.com/download/account/f_11760151041/0/HICKER95RICKBOX.xls<o></o>

That is not the link they gave you for your file... we cannot use that to retrieve your file unless we log into your account. Try uploading the file to box.net again, but once it is fully uploaded, look around on the page for the link they give you for the file to give out to others... that is what we need.

 

[montecarlo2012]

Sorry, I will, thank you.

 

[montecarlo2012]

Here we go:Box

 

[Rick Rothstein]

Here we go:https://app.box.com/s/mkj59obhb8nzrxvy6os6

Okay, I got it. If I understand correctly, this code should work for you...

Sub DoIt()
  Dim LastRow As Long, LastCol As String
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  LastCol = Split(Columns("P").Resize(, LastRow - 1).Address(0, 0), ":")(1)
  With Range("H2:M" & LastRow)
    .Formula = "=INDEX($P$2:$" & LastCol & "$54,B2,ROW(A1))"
    .Value = .Value
  End With
End Sub

NOTE: You uploaded an Excel file with an XLS extension which means it has only 256 total columns; however the number of rows of data in Column B:G were over 2000... that means you would not have enough columns to supply numbers for each row in Columns B:G... you will need to use an XLSM file extension (available with XL2007 or above) in order to have enough columns and also retain the macro code. I'm sure you already know that, just making sure you remembe to save the file out with the correct extension so you do not lose data.

 

[montecarlo2012]

Rick Rothstein, thank you. when I save the file I make a mistake but now I am putting in the right version, 2010. Again you help me. work as long as I have to complete the columns, well this is going to be another thread. thank you hicker95.

 

[montecarlo2012]

good morning Rick, sorry about this, I realize that the results are not in the right position

5	12	14	28	45	50	5	12	14	28	45	50	Q results swap here
9	12	15	21	33	45	2	2	5	2	1	2	R results swap here

the row B2:G2 supposedly has to be swap for the corresponding values in column "Q", and here you will see the row is repeat it, so in the next row the values in the green zone will be column "R". and so on. I realize this today sorry. you can see in my box net I indicate like this :[

5	12	14	28	45	50	P	P	P	P	P	P
9	12	15	21	33	45	Q	Q	Q	Q	Q	Q
20	25	29	30	36	37	R	R	R	R	R	R
2	11	13	20	27	28	S	S	S	S	S	S
4	6	15	24	47	52	T	T	T	T	T	T

Is what value row B2:G2 has in P and swap and so on. thank you. I see I said in one part Q and the next P, the thin is I tried to change the result from H:M to I:N With Range("I2:N" & LastRow) but do not work, sorry.

 

[hiker95]

montecarlo2012,

Well it looks like I may have it wrong again, based on your last reply - but, I will give it a try anyway.

Sample raw data:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	5	12	14	28	45	50									1	2	2	2	2	2	2
3	9	12	15	21	33	45									2	3	3	2	2	2	2
4	20	25	29	30	36	37									3	3	3	3	4	4	5
5	2	11	13	20	27	28									4	2	2	2	1	1	0
6	4	6	15	24	47	52									5	1	1	1	1	1	0
7	10	16	21	25	37	49									6	1	2	2	1	1	2
8	4	5	8	13	27	44									7	1	1	1	1	1	1
9	2	3	15	39	44	49									8	3	3	3	3	3	2
10	19	23	28	38	48	53									9	1	1	1	2	2	2
11	3	8	16	20	43	48									10	2	2	3	3	2	2
12	13	16	17	40	42	44									11	1	1	1	2	3	3
13	1	12	22	44	46	52									12	1	1	2	2	2	2
14	10	13	35	36	37	42									13	4	4	3	3	3	2
15	9	26	34	40	42	49									14	1	2	2	2	2	2
16	1	2	25	43	48	53									15	4	4	4	3	3	3
17	8	32	35	46	47	52									16	4	4	4	4	3	3
18	14	15	23	36	49	50									17	2	2	3	3	3	3
19	15	17	22	40	41	45									18	0	0	0	0	0	0
20	3	26	36	37	43	44									19	1	1	1	1	1	1
21	7	16	31	38	39	42									20	3	2	1	1	1	1
22	6	14	37	41	48	49									21	1	1	1	2	1	1
23	10	11	12	17	28	46									22	2	2	2	2	2	2
24	3	9	11	21	24	49									23	2	2	2	2	2	2
25	11	24	36	45	46	47									24	1	1	1	1	2	2
26	3	6	36	46	48	52									25	3	2	2	2	1	1
27	2	7	11	15	43	45									26	2	2	2	2	2	2
28	3	6	16	30	31	40									27	2	2	1	1	1	0
29	20	44	46	48	52	53									28	2	2	2	2	2	2
30	29	33	46	48	49	53									29	1	0	0	0	0	0
31	3	4	5	39	47	48									30	1	0	0	0	0	0
32	4	8	17	20	51	52									31	1	1	1	1	1	1
33	6	10	18	27	28	51									32	1	1	1	1	1	1
34	4	8	15	16	32	52									33	0	0	0	0	0	0
35	8	21	22	27	40	44									34	1	1	1	1	1	1
36	1	3	4	12	15	38									35	2	2	2	2	2	2
37	2	10	11	41	42	53									36	4	3	3	3	4	5
38	13	17	20	23	24	30									37	4	4	4	4	3	3
39	1	7	35	39	43	47									38	2	2	2	2	2	2
40	3	13	42	47	52	53									39	2	2	2	2	2	2
41	1	3	5	14	28	32									40	3	3	3	3	3	3
42	5	13	18	28	31	43									41	1	2	2	2	2	2
43	2	7	31	40	46	52									42	4	4	4	4	4	4
44	5	8	9	12	23	27									43	3	3	3	3	3	3
45	3	16	19	20	32	52									44	5	5	5	5	5	4
46	2	29	35	37	41	42									45	1	1	1	1	2	2
47	21	24	34	41	47	52									46	2	2	3	3	4	5
48	13	19	22	44	46	52									47	2	2	2	1	2	2
49	5	15	29	35	40	47									48	3	4	4	4	4	5
50	4	36	42	47	48	52									49	4	5	5	6	5	5
51	1	21	26	30	46	48									50	1	1	1	1	1	1
52	21	24	33	37	43	46									51	0	0	0	0	0	0
53	1	11	19	33	37	49									52	3	3	3	2	2	3
54	5	6	13	33	35	40									53	2	2	2	2	2	2
55	15	17	30	42	44	49
Sheet1

After my latest macro:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	5	12	14	28	45	50		1	1	2	2	2	1		1	2	2	2	2	2	2
3	9	12	15	21	33	45		1	1	4	2	0	2		2	3	3	2	2	2	2
4	20	25	29	30	36	37		3	2	0	0	4	3		3	3	3	3	4	4	5
5	2	11	13	20	27	28		3	1	3	1	1	2		4	2	2	2	1	1	0
6	4	6	15	24	47	52		2	2	4	1	2	3		5	1	1	1	1	1	0
7	10	16	21	25	37	49		2	4	1	2	3	5		6	1	2	2	1	1	2
8	4	5	8	13	27	44		2	1	3	3	1	4		7	1	1	1	1	1	1
9	2	3	15	39	44	49		3	3	4	2	5	5		8	3	3	3	3	3	2
10	19	23	28	38	48	53		1	2	2	2	4	2		9	1	1	1	2	2	2
11	3	8	16	20	43	48		3	3	4	1	3	5		10	2	2	3	3	2	2
12	13	16	17	40	42	44		4	4	3	3	4	4		11	1	1	1	2	3	3
13	1	12	22	44	46	52		2	1	2	5	4	3		12	1	1	2	2	2	2
14	10	13	35	36	37	42		2	4	2	3	3	4		13	4	4	3	3	3	2
15	9	26	34	40	42	49		1	2	1	3	4	5		14	1	2	2	2	2	2
16	1	2	25	43	48	53		2	3	2	3	4	2		15	4	4	4	3	3	3
17	8	32	35	46	47	52		3	1	2	3	2	3		16	4	4	4	4	3	3
18	14	15	23	36	49	50		1	4	2	3	5	1		17	2	2	3	3	3	3
19	15	17	22	40	41	45		4	2	2	3	2	2		18	0	0	0	0	0	0
20	3	26	36	37	43	44		3	2	3	4	3	4		19	1	1	1	1	1	1
21	7	16	31	38	39	42		1	4	1	2	2	4		20	3	2	1	1	1	1
22	6	14	37	41	48	49		1	2	4	2	4	5		21	1	1	1	2	1	1
23	10	11	12	17	28	46		2	1	2	3	2	5		22	2	2	2	2	2	2
24	3	9	11	21	24	49		3	1	1	2	2	5		23	2	2	2	2	2	2
25	11	24	36	45	46	47		1	1	3	1	4	2		24	1	1	1	1	2	2
26	3	6	36	46	48	52		3	2	3	3	4	3		25	3	2	2	2	1	1
27	2	7	11	15	43	45		3	1	1	3	3	2		26	2	2	2	2	2	2
28	3	6	16	30	31	40		3	2	4	0	1	3		27	2	2	1	1	1	0
29	20	44	46	48	52	53		3	5	3	4	2	2		28	2	2	2	2	2	2
30	29	33	46	48	49	53		1	0	3	4	5	2		29	1	0	0	0	0	0
31	3	4	5	39	47	48		3	2	1	2	2	5		30	1	0	0	0	0	0
32	4	8	17	20	51	52		2	3	3	1	0	3		31	1	1	1	1	1	1
33	6	10	18	27	28	51		1	2	0	1	2	0		32	1	1	1	1	1	1
34	4	8	15	16	32	52		2	3	4	4	1	3		33	0	0	0	0	0	0
35	8	21	22	27	40	44		3	1	2	1	3	4		34	1	1	1	1	1	1
36	1	3	4	12	15	38		2	3	2	2	3	2		35	2	2	2	2	2	2
37	2	10	11	41	42	53		3	2	1	2	4	2		36	4	3	3	3	4	5
38	13	17	20	23	24	30		4	2	1	2	2	0		37	4	4	4	4	3	3
39	1	7	35	39	43	47		2	1	2	2	3	2		38	2	2	2	2	2	2
40	3	13	42	47	52	53		3	4	4	1	2	2		39	2	2	2	2	2	2
41	1	3	5	14	28	32		2	3	1	2	2	1		40	3	3	3	3	3	3
42	5	13	18	28	31	43		1	4	0	2	1	3		41	1	2	2	2	2	2
43	2	7	31	40	46	52		3	1	1	3	4	3		42	4	4	4	4	4	4
44	5	8	9	12	23	27		1	3	1	2	2	0		43	3	3	3	3	3	3
45	3	16	19	20	32	52		3	4	1	1	1	3		44	5	5	5	5	5	4
46	2	29	35	37	41	42		3	0	2	4	2	4		45	1	1	1	1	2	2
47	21	24	34	41	47	52		1	1	1	2	2	3		46	2	2	3	3	4	5
48	13	19	22	44	46	52		4	1	2	5	4	3		47	2	2	2	1	2	2
49	5	15	29	35	40	47		1	4	0	2	3	2		48	3	4	4	4	4	5
50	4	36	42	47	48	52		2	3	4	1	4	3		49	4	5	5	6	5	5
51	1	21	26	30	46	48		2	1	2	0	4	5		50	1	1	1	1	1	1
52	21	24	33	37	43	46		1	1	0	4	3	5		51	0	0	0	0	0	0
53	1	11	19	33	37	49		2	1	1	0	3	5		52	3	3	3	2	2	3
54	5	6	13	33	35	40		1	2	3	0	2	3		53	2	2	2	2	2	2
55	15	17	30	42	44	49		4	2	0	4	5	5
Sheet1

In order to continue, please supply another workbook containing all the column groups - 6 columns, 6 columns, and the output area lookup numbers in 1 column, and the 6 column table that holds the answers.


If you are notable to provide an accurate display of what we should be working with, then:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[HGAndresen]

You can easily get this done by using vlookup. vlookup searches for a value in the first column of a range and returns a value from the same row but from another specified column.

vlookup(search value, range to look in first column for a match and from where to return a value from same row, the column number from where to pick)

So all you need is to specify which column you want values from, thus

1. Insert a column to the left of a, and number the rows with data 2,3,4,5 etc (start at two, 2)

column a will now be column b, b will be c etc. In the following line CR is the address of the last (bottom right) cell in your lookup range.


2. in i2 write =vlookup(B2,$P$2:$C$R,$A2)
copy across and down.


That will look for the value in b2 (9) in column p (match in row10) and return the value from the column number that is found in a2 (1) . As you copy across all values will be picked from the same column. When you copy down , the values will be picked from columns further to the right.