Searching the row that contains the same two columns
 

Hi,
I have two lists. Each list contains two columns. Both columns in a row
represent an identification that is unique. For example, collumn A = 114555
and the B = 104. Those two columns are in columns A and B and the other list
exactly the same one but in a completly different order is in columns AG and
AH. So the code must search for each identification from combination AG and
AH to find where is the same combination in columns A and B and then copy
columns AG to BL from the row from combination AG and AH to this new location
(columns A to AF) that was found with the 2 cells value from columns A and B.

Note that the list is dynamic. So I use the code line in the code tag below
to determine the number of rows (the two lists are in the same range of rows
and have the same number of rows.) Also, the main reason why I do that is
because I have a complexe sorting function that works well but that does not
copy the values attached to their identification when those identifications
moves (see also the link below to see that special sorting function if you
want).

Link :
http://www.ozgrid.com/forum/showthread.php?t=22170

Could anybody help me on that one?

Thx!

Werner


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200507/1

Searching the row that contains the same two columns
 

I am a little confused by your description but

You can use the following funtions:
Countif(Range("AH:AH"),cell value) to determine if a cell value is in
another range
Match(cell valule,Range("AG:AG"),0) to determine the row that the cell
value is in the other range
Index(Range("AH:AH"),Match(cell valule,Range("AG:AG"),0),1) to return
the value from AH

also checkout Lookup()

to use in code

dim results

results = WorksheetFunction.FunctionName(arguments)

see if this helps...
--
steveB

Remove "AYN" from email to respond
"Jean-Jerome Doucet via OfficeKB.com" wrote in message
...
Hi,
I have two lists. Each list contains two columns. Both columns in a row
represent an identification that is unique. For example, collumn A =
114555
and the B = 104. Those two columns are in columns A and B and the other
list
exactly the same one but in a completly different order is in columns AG
and
AH. So the code must search for each identification from combination AG
and
AH to find where is the same combination in columns A and B and then copy
columns AG to BL from the row from combination AG and AH to this new
location
(columns A to AF) that was found with the 2 cells value from columns A and
B.

Note that the list is dynamic. So I use the code line in the code tag
below
to determine the number of rows (the two lists are in the same range of
rows
and have the same number of rows.) Also, the main reason why I do that is
because I have a complexe sorting function that works well but that does
not
copy the values attached to their identification when those
identifications
moves (see also the link below to see that special sorting function if you
want).

Link :
http://www.ozgrid.com/forum/showthread.php?t=22170

Could anybody help me on that one?

Thx!

Werner

Code:

---


Let lstRw = Sheets("Formulaire").Range("a65536").End(xlUp).Row



---

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200507/1

Searching the row that contains the same two columns
 

I wouldnt use VBA but functions.

First: setup some "dynamic named ranges"

e.g.
tbl =OFFSET(Formulaire!$AG$2,0,0,COUNTA(Formulaire!$AG :$AG)-1,32)
idx =INDEX(tbl,,1)&INDEX(tlb,,2)
or in french
tbl =DECALER(Formulaire!$AG$2;0;0;NBVAL(Formulaire!$AG :$AG)-1;32)
idx =INDEX(tbl;;1)&INDEX(tlb;;2)

note this requires there are no "gaps" in the data in AG.
the tbl formula assume 1 header row.
the idx formula concatenates the 2 columns.

Then in c2 we're going to find the record's position..
c2 =MATCH(A2&B2,Idx,0)
d2 =INDEX(tbl;c2;COLUMN(d2))

c2 =EQUIV(A2&B2;idx;0)
d2 =INDEX(tbl;$C2;COLONNE(D2))

copy d2 sideways
then copy the row down as needed.






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jean-Jerome Doucet via OfficeKB.com wrote :

Hi,
I have two lists. Each list contains two columns. Both columns in a
row represent an identification that is unique. For example, collumn
A = 114555 and the B = 104. Those two columns are in columns A and B
and the other list exactly the same one but in a completly different
order is in columns AG and AH. So the code must search for each
identification from combination AG and AH to find where is the same
combination in columns A and B and then copy columns AG to BL from
the row from combination AG and AH to this new location (columns A to
AF) that was found with the 2 cells value from columns A and B.

Note that the list is dynamic. So I use the code line in the code tag
below to determine the number of rows (the two lists are in the same
range of rows and have the same number of rows.) Also, the main
reason why I do that is because I have a complexe sorting function
that works well but that does not copy the values attached to their
identification when those identifications moves (see also the link
below to see that special sorting function if you want).

Link :
http://www.ozgrid.com/forum/showthread.php?t=22170

Could anybody help me on that one?

Thx!

Werner

Code:

---


Let lstRw = Sheets("Formulaire").Range("a65536").End(xlUp).Row



---

Searching the row that contains the same two columns
 

I'll check that monday morning when I am back at my office.

Thx for the help and have a good weekend.

Regards,

JJD


--
Werner
------------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304
View this thread: http://www.excelforum.com/showthread...hreadid=385736