Searching the row that contains the same two columns

[Jean-Jerome Doucet via OfficeKB.com]

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

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

 

[STEVE BELL]

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...

 

[keepITcool]

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 :

 

[Werner]

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

Thx for the help and have a good weekend.

Regards,

JJD