Searching one sheet and copying row to another sheet

forumtest.xlsx
If you can figure out how to do this with formulas I'll be fully satisfied. I can't though.

I have a workbook with two sheets. Sheet1 contains a set of information about orders. Sheet2 has a different set of info about orders. What I'm looking to do is run a macro that will check to see if the order numbers are the same and if they are copy the data from Sheet1 and put it in the same row but next to the line that has the same order in Sheet2.

Sheet1 has the order numbers in column E whereas Sheet2 has the order numbers in column B. An additional complication is that the order numbers in the sheets are different formats. They are 123456 in Sheet1 but might be AA123456 or B123456 or 123456 in Sheet2.

My current thoughts were running a loop, for every row in Sheet1, setting the value of Erow to ordernumber, copying the row, moving to Sheet2, searching for ordernumber. If it's found, paste the values starting in column O of Sheet2 in the same row the value is found. But I can't make that work. I also don't know if there is an easier way to do this. Any ideas? Thanks

You should be able to do this with just formulas, VBA may not be necessary.

To be certain, click GO ADVANCED and use the paperclip icon to post up a sample copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use two versions of sheet2, BEFORE/AFTER sheets, if that helps make it clearer. There just needs to be enough data in the sample to clearly demonstrate to us the variations that must be dealt with.

You used the word "random data" in every cell making it essentially useless. Even the titles?
This will make is much harder for you to see the results actually working.

Here's your sheet back with actual randomized data. Now mockup sheet2 the way it would appear based on that, show the answers you expect.

Put this formula in R5:

=REPLACE(B5,1,MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},B5&1234567890))-1,"")+0

Put this formula in S5 and copy across:
=IFERROR(INDEX(Data!A:A, MATCH($R5, Data!$E:$E, 0)), "")

Format the various cells on row 5 the way you need.

Now just copy R5:AE5 down.

Here's the VBA method:

Please Login or Register  to view this content.