Automated search and copy lots of data

**Xelpme** · 05-11-2007, 03:02 PM

I think what I require is some code that searches another column to find if it contains the same information (product ID) in the present cell, then copy information several rows across that belongs to that product ID.

So a mini example would work like this.

Col A---Col E----Col F---Col G
BE2 AE1 2 4
AE1 DE2 3 8
WA2 ER2 3 2
ASE2 BE2 2 3

You start off in the present cell in Col A which is BE2. You then search Col E until a match is found, so a loop is required, row 1 doesn't match, nor 2 and so on until you find it which is in row 4. Once there is a match copy Columns E,F,G into same row Columns B,C,D.
Then the process starts again. You are now in row 2 of Col A, you go to the top of Col B and search down until a match is found. Straight away a match is found, so copy all data to B,C,D.

Col A contains extracted data, the other columns contain imported data. The purpose is to compare, match and copy the data. It would work as an advanced form of sort, instead of sorting by a-z or number, it would sort by another column's data.

The only problem is, how do I do this with a macro?

The second problem is how to quickly sort alphanumeric data like:
AB101
AB12
AB110

12 should be first, then 101, then 110, but excel puts 101 first, then 110 and then 12.

Ant help appreciated.

**mrice** · 05-12-2007, 08:51 AM

Your first problem is solved easiest with the VLOOKUP function.

Paste into B1

=IF(ISNA(VLOOKUP($A1,$E$1:$G$4,1,0)) = FALSE,VLOOKUP($A1,$E$1:$G$4,1,0),"")

into C1

=IF(ISNA(VLOOKUP($A1,$E$1:$G$4,2,0)) = FALSE,VLOOKUP($A1,$E$1:$G$4,2,0),"")

and D1

=IF(ISNA(VLOOKUP($A1,$E$1:$G$4,3,0)) = FALSE,VLOOKUP($A1,$E$1:$G$4,3,0),"")

and then copy the three new entries down.

The second one can be down with a couple of user defined functions

Function Letters(Cell)
For N = 1 To Len(Cell)
    If IsNumeric(Mid(Cell, N, 1)) = False Then
        Letters = Letters & Mid(Cell, N, 1)
    End If
Next N
End Function


Function Numbers(Cell)
For N = 1 To Len(Cell)
    If IsNumeric(Mid(Cell, N, 1)) = True Then
        Numbers = Numbers & Mid(Cell, N, 1)
    End If
Next N
Numbers = Val(Numbers)
End Function

Put these on a module in the VBA editor. Use then like