Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to synchronize data from 2 tables or cells?

0

How can I "synchronize" data between 2 tables or 2 cells?

This is hard to explain....

But basically I want to "merge" data between 2 cells or colums.

I want data from one table to overwrite the data in target destination, overwrite what is there, but not add new data. 

** addded new file called SyncDataExplanation" **

Answer
Discuss

Discussion

Hi and welcome) - In the MergeExample2 file, why does Number 2 in Table C not equal C when it has C in it for Table A? Maybe I'm not understanding how you want to merge the data.
don (rep: 1989) May 16, '19 at 8:45 am
Thanks, so hard to explain...I'm probably not very good at it!

The data in numbers columns will not change, but the order will.

The data in letters columns will change.

The data from table B needs to "fuse, merge, sync, consolidate, combine" into table B

If you put table A and table B together , the result in table C.

Like this:

https://en.wikipedia.org/wiki/Data_synchronization
brunovincent May 16, '19 at 8:28 pm
It may be hard to explain but without an explanation an answer can't be provided. Please review your method of explanation: you need to establish generic rules by which columns are merged. (1) Rules for numbers, (2) rules for letters, (3) rules for relationship between numbers and letters on the same row. (1) Merge all numbers in both tables and sort in serial order. (2) Merge all letters in both tables and sort in alphabetical order. (3) No idea what you want. Please don't refer us to other sites to pick of details of your request.
Variatus (rep: 4889) May 16, '19 at 9:07 pm
This sort of looks like an exam or project. The Url problem could probably be solved with a vlookup.
Single column merge could be done with something along the lines of for every item in column 2 that is not in column 1 add it to column 1 then sort it once you have finished all of column 2.
The 2 columns to 2 columns defy logic at this point. If I had to merge those I would have wound up with 1-a, 2-Null, 2-c, 3-null, 3-c
k1w1sm (rep: 197) May 16, '19 at 10:31 pm
The plot thickens. By which rule would "Dog Black" + "Cat Red" become "Dog Green"? The shortest way to a solution should be if you would tell us what you really want to do. There is a substantial difference between numbers, URLs and animals (words) as there is between single letters and colours (presumably also words). If all you want is to create a list of the current status of a list of URLs just saying so would take us a huge step forward.
Variatus (rep: 4889) May 16, '19 at 11:26 pm
Thanks everybody for trying to understand me! Ok, let me try explain in normal words then;)

Say I have a list of 1000 websites.

Step 1. I check to see what sites are responsive or not.
Step 2. Result is 100 websites are not responsive.
Step 3.I want to paste back the 100 websites to the  original 1000 websites list.

If I do that, I will have 1100 websites and duplicates. The order will be lost also...

How do I paste back the defective 100 sites into the 1000 sites list, and keep it all in order?

Sorry...I hope this is clearer? The data would have 2 columns I guess?

Column A would be "URL" and column B would be "Status" with data as [Yes] or [no] or maybe {repsonsive} {non-responsive}
brunovincent May 16, '19 at 11:50 pm
Add to Discussion

Answers

0

There is no "merging" of lists. The task is accomplished by marking all items on the original list as "responsive" if they are NOT included in the second list, and as "unresponsive" if they also appear in the list of unresponsivle URLs. This is the formula that does the job.

=IF(LEN($A2),ISNA(MATCH($A2,'Failed URLs'!$A$3:$A$100,0)),"")

Note that the lookup range of A3:A100 is intentionally much larger than required. If you expect a list of unresponsivkle URLs of 500 make the range comprise 1000 cells, just so that you don't have to worry about it being too small.

At the core of this formula there is the MATCH function which looks for a match in the "failed" list for each item in the "Original" list. MATCH is embedded in the ISNA function which translates a successful match as FALSE and an unsuccessful one as TRUE, meaning if the URL was found in the list of failures ISNA returns FALSE which the list presents as a Status of "FALSE" = "Unresponsive". The entire formula is then embedded into an IF statement which creates a blank cell if there is no URL to look up. This happens when the formula is next to cells without an URL, at the end of the list.

Discuss

Discussion

I notice that you selected my answer and then changed your mind. What is it that my solution didn't do the way you want?
Variatus (rep: 4889) May 17, '19 at 10:54 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login