Importing data but does not overwrite.

[Sprackers]

Hello fellow Excellers,

Im new here so hello and here we go and bear with me.

I have a spread sheet already with names and values. This is an example real one has (1500 names.)

New List

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]









I now have an old (yes old) list that I want to update the new list with.

the Old List has values but I do not want to overwrite the values in the New List but in the Old List, Fred has a value that I DO want to be added.

Old List

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Names[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]









What I'm looking for, and I'm guessing here is something like......IF cell is blank then input data, if it's not blank then do nothing.

I hope this is clear and thanks for your help.

Sprackers

 

[Sprackers]

Re: Hello Excel Friends importing data but does not overwrite.

Hi Joe,

I tried your Trim but I had the same result. I think there something fundamentally wrong with the original formula.


=IF(B2<>"",B2,IFERROR(VLOOKUP(B2,Old-list,2,0),"Not Found"))

Step by step

=IF(B2<>"",B2 ................If there is something in the cell B2, then use the value that's in B2- that's pretty simple. ie keep it the same.

..., IFERROR - So this is saying if there is nothing in the B2 then use the following formula to fill this cell

(VLOOKUP (B2,old-list,2,0) - so this saying scan column B2 in the "old-list" for the name of the client, then look in column 2 (this will be all the phone numbers), and put that value into the new list.

"0" means we want an exact match on the names.

, "Not Found") - means there is no number on the old list to put into the blank cell on the new list or there is no name on the old list that matches the new list.?

Big question mark after all the above but we are so close and with 888 names to put in this would save everything.

Thanks Joe.

 

[Joe4]

Re: Hello Excel Friends importing data but does not overwrite.

If your lookup cannot find a match, it will return an ERROR. So what we are doing with the IFERROR statement is saying if it cannot find a match and returns an error, return the phrase "Not Found" instead of returning that error.

I think your issue may be with your named range. Do you know how to create named ranges?
I notice that you are using "old-list", which sticks out like a sore thumb, because named ranges do not allow dashes in their names. So you CANNOT have a name range like "old-list".

If you need help on creating named ranges, see: Named Ranges

 

[Sprackers]

Re: Hello Excel Friends importing data but does not overwrite.

Hi Joe,

I only used "old -list" to keep in sync with earlier threads, I understand about highlighting the whole data table.

I know I probably did explain the problem very well so I have recapped below .....hope it helps



List A

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]111[/TD]
[/TR]
</tbody>[/TABLE]











List B (notice the names are in different order and there is a new one Sebastian)





[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Names[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]222
[/TD]
[/TR]
[TR]
[TD]Sebastian[/TD]
[TD]222[/TD]
[/TR]
</tbody>[/TABLE]










What I was hoping to achieve was, after the Vlookup formula was a table like this where List A has been updated from List B but only if there is a blank space (Fred) AND added Sebastian.


New List

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Sebastian[/TD]
[TD]222[/TD]
[/TR]
</tbody>[/TABLE]












I really hope this may clear up my bad explanation earlier.

Many thanks

Phil

 

[Joe4]

Re: Hello Excel Friends importing data but does not overwrite.

As I said a few times, the fact that they are in a different order makes absolutely no difference. So there is no need to worry about that. VLOOKUP is scanning the list for a match, so order does not play any role at all if you are doing exact matches.

You did introduce a new wrinkle though - people who are in List B who are not on List A. Will either of these Lists show everybody? Or might there also be people on Lisa A who are not on List B.
What we really need to get is a COMPLETE list of ALL people. If neither list has that, it is going to make this much trickier. Quite frankly, Access handles this kind of matching much better than Excel does.

Going back to your formula:
- Are you "Lists" really data tables?
- Can you post the EXACT formula you are trying (do not change any of the details to try to keep it consistent with earlier suggestions - I cannot spot typos or formula errors if you do that)?