Moving excel cells/rows

[jent]

I created a long Excel document with about ten thousand rows of items, comparing two different sets of data. Between two columns with the two sources of data I was comparing I created a column with a formula that said "Same" if the numbers were the same, or displayed the difference if the numbers weren't the same.

Now, one of those two data sources has been updated. Instead of both sets having the same number of items (about 10,000), the updated one totals to about 10,500. Is there any kind of code or script that can check one column's cell against another cell in that row, and if the numbers are the same, leave them there, but if they're not, shift it down until it matches?

This is a difficult task since a few of the extra five hundred items don't have a matching number in the second data column. For example, one row might have item number 2060 in both columns, but with the updated data, the first column now has numbers 2061 and 2062 while the second column goes right to 2070. If I can clarify anything, please let me know!

 

[wrldwzrd89]

I created a long Excel document with about ten thousand rows of items, comparing two different sets of data. Between two columns with the two sources of data I was comparing I created a column with a formula that said "Same" if the numbers were the same, or displayed the difference if the numbers weren't the same.

Now, one of those two data sources has been updated. Instead of both sets having the same number of items (about 10,000), the updated one totals to about 10,500. Is there any kind of code or script that can check one column's cell against another cell in that row, and if the numbers are the same, leave them there, but if they're not, shift it down until it matches?

This is a difficult task since a few of the extra five hundred items don't have a matching number in the second data column. For example, one row might have item number 2060 in both columns, but with the updated data, the first column now has numbers 2061 and 2062 while the second column goes right to 2070. If I can clarify anything, please let me know!

I've seen this sort of issue before. There's a function built into Excel that can help, though: VLOOKUP. It searches vertically through a table (which can have just one column in it, and which you define) and returns a value in the same row from a column you specify. An example may help clarify how this would be used. Let's say your "source" data is in column A, your "compare against" data is in column C (which isn't the same length as A), and your "formula area" is column B. Let's also assume that column C has exactly 10,500 data rows in it. That being the case, this little formula will tell you if a given item in A exists in C:

=IF(ISERROR(VLOOKUP(A1, $C$1:$C$10500, 1, FALSE)), "Does NOT Exist", "Exists")

Those dollar signs are important - they lock your data range in place so that Excel's fill down feature will work properly.

 

[thatisme]

check youtube for videos that cover "dynamic ranges". there are tons of helpful vids on the subject, and it may be able to cover what you are looking to do. I find the tutorials from Excelisfun some of the better ones out there. You may have to watch a few of them before you find one that covers your issue.

..I am not affiliated in any way with the author of the videos... I just happened to have spent a pretty significant amount of time researching my own Excel issue on youtube.

Question: do your data sets only contain UNIQUE numbers/data? If not, Vlookup may not work well for you as it doesn't handle duplicates