VBA Code to fill cells based on info from another sheet

[tinydancer]

So I have a long and somewhat complex problem, let's hope I can describe it properly. I have two sheets of data, one data dump for the previous week and one for the current week. I need some data from the previous weeks sheet to carry over to the current one. Starting with the previous weeks sheet, in one column (E) I have a list of unique order numbers which is automatically generated from a data dump and not repeated in the column. A few columns over (columns Q, R, and S) I have info which is specific to that order number and manually added based off the order number in the given row. Now in the current weeks sheet, some of those order numbers from the previous week are still present in the new data dump as they have still not yet been completed. For those order numbers that are still present, I want the information in columns Q, R, and S for that order number to copy over from the old sheet to the new one. The information in those last three columns is not automatically generated so having a code or possibly an IF statement to copy them over automatically would be a real time saver as the dump itself usually contains anywhere from 400-1000 rows of data. This code or statement needs to work continually as well, as this current weeks data dump will become the previous weeks data dump and so on. I really appreciate any form of help that can be offered, don't hesitate to ask questions if need be.

 

[arviy2k]

Since you mentioned the order number would be unique, the simplest solution would be to do a VLOOKUP for columns Q, R and S from previous week sheet to current week sheet.

Syntax: =VLOOKUP('Lookup Value', 'Table Array', 'Col Index Number', 'Match Type')

In your example, your formula would be =VLOOKUP('Current sheet-E1, 'Entire previous sheet table, from column E to S', counting from E - column number- so E would be 1, F-2, G-3 and so on, Match type FALSE)

Whatever order number is not found in previous week sheet will throw a #N/A error, you can just filter the data and delete these.

Hope this helps.

Regards,
ArvindYoga

 

[tinydancer]

What you are saying makes sense. I should have known about VLOOKUP, but it still isn't work right. I keep getting #N/A for every cell I apply this to and I don't know why. I tried it exactly how you said it and a few others and I still keep getting #N/A for every cell. Any idea why?

 

[arviy2k]

Example Previous Sheet:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]...[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1111[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2222[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3333[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]







Example Current Sheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]...[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2222[/TD]
[TD][/TD]
[TD]=vlookup(E1,Previous Sheet!E1:S3,13,FALSE)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4444[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]E[/TD]
[TD]...[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1111[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2222[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3333[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]
As per the formula, it will lookup '2222' (E1) in current sheet, with table of E1:S3 of previous sheet, and return column 13 (Q is the 13th column after E)

Hope this helps. I think your formula is right, but you might be selecting the wrong table. The table has to start with the order number column.

Also, check if there are any spaces in the order number. If the previous sheet has '2222 ' (blank spaces after the number), lookup with '2222' will not work, as excel identifies space as a character.

 

[Peter_SSs]

@arviy2k
Please be careful what you include in your post. Some HTML in your post had stopped this thread from appearing correctly.

 

[tinydancer]

Any way to put what you suggested into a VBA code @arviy2k? I have multiple rows all of which will search the same array for their respective value in column E and display what ever is in the other column Q.