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

 

[Joe4]

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

Welcome to the Board!

You can use the VLOOKUP function to lookup a value in another list.
See: MS Excel: How to use the VLOOKUP Function (WS)

So, if you wanted to do the whole thing in column C, and say if the value in column B has a value, return that value, otherwise lookup the value from the other list, your formula could be structured something like this:

=IF(B2<>"",B2,IFERROR(VLOOKUP(B2,Old_List,2,0),"Not Found"))

(using a named range for "Old_List")

 

[Sprackers]

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

Wow thanks for you swift reply Joe.

looks like Ive got a lot of studying to do on Vlookup.

Many thanks im going to give it a go.

Sprackers

 

[Sprackers]

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

Ive had alook and teh problem is with the above the value that would be inserted is a static value. my value would be on a list of 1500.

So in a nutshell

Example:

Is Cell B4 on "New List" empty? if Yes - Update data from Cell B4 "Old List"
If No - Do Not update anything.

Is Cell B5 on "New List" empty? if Yes - Update data from Cell B5 "Old List"
If No - Do Not update anything.

and so on until B1256.....

Does your formula work or is there a simpler way?

Many thanks

Sprackers

 

[Joe4]

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

Is Cell B4 on "New List" empty? if Yes - Update data from Cell B4 "Old List"
If No - Do Not update anything.

No, that is not how VLOOKUP works. If you were looking to take a value from the exact same cell in another list, you would not need VLOOKUP.
VLOOKUP will search for the value it is matching on across the whole list, and then return the corresponding value from the column next to it.
So with VLOOKUP, the lists of people do not need to be the same size or even in the same order.

 

[Sprackers]

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

Hi Joe,

Ive managed to find a way if all the names are in exactly the same order in the original sheet and the update info.

=IF(B2<>"",B2,G2) where Column B is the original list and Column G is the details I want to update to column B.

This works well but it means I have to order all the names in the same position.

What you are saying is Vlookup will mean that the columns don't need to be in the same order?

VLookup will take the new update info, find the correct account name, and update the relevant details?

Many thanks for your patience Joe, its a great help.

Sprackers

 

[Joe4]

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

What you are saying is Vlookup will mean that the columns don't need to be in the same order?

That is correct.

As long as you aren't using approximate matches, the order does not matter.
Did you read through the link I gave you? It explains it all in great detail and gives examples.

 

[Sprackers]

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

Hi Joe,

I did go through the link but found it unclear and difficult to understand.

I'm going to go back through it again and again and again.

I have to find out how to use this.

I may come back for help if I get stuck.

Many thanks again

Sprackers

 

[Sprackers]

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

Hi Joe

I tried the below formula but it didn't insert the new number.

I the blank space it just read "Not Found" I needed the formula to, instead of just putting not found, to grab the number for list 2 and insert it into list one.

I have read and watched many articles on Vllokup and it not clear on any of the tutorials.

Any help would be great.

=IF(B2<>"",B2,IFERROR(VLOOKUP(B2,Old_List,2,0),"Not Found"))

 

[Joe4]

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

So, are you saying that B2 is not really empty, but has a single blank space in it?
If so, then make this modification:

[COLOR=#333333]=IF([/COLOR][COLOR=#ff0000]TRIM(B2)[/COLOR][COLOR=#333333]<>"",B2,IFERROR(VLOOKUP(B2,Old_List,2,0),"Not Found"))[/COLOR]

Trim will drop/ignore spaces.

Basically, what we are saying is if B2 has anything it all in it, then use that. Otherwise use the VLOOKUP. However, a blank space IS something, and is different than nothing.
So, since it sounds like you have blank spaces and not really empty cells, we must account for it.
You will only get "Not Found" if there is something in B2, but it cannot find a match in Old_List for that value.