Nov 30 2017 03:13 PM - edited Nov 30 2017 04:54 PM
Hello,
I need help in comparing column B to column A and pulling that duplicate information to column D. My dilemma is I have three columns A, B, C. B and C are connected, column B has the ID information and Column C has the Date. When comparing column B to A, I need the results along with the date to go to column D (ID information) and column E (Date). Blue fonts are my anticipated results. I hope I am explaining myself clearly. I have about 2000 records that I need to compare. I was told that I can do this in Excel 2016 using VLOOKUP or ACCESS database, but I am not familiar on how to do this. Excel info would be great. Any help is greatly appreciated, thanks.
Nov 30 2017 09:21 PM - edited Nov 30 2017 09:22 PM
SolutionArnold,
Step 1
Put this formula in cell D2 and fill it down:
=IF(ISNUMBER(MATCH(B2,$A$2:$A$200,0)),B2,"")
Don't forget to change this range $A$2:$A$200 to your existing range.
Step 2
Put this formula in cell E2 and fill it down:
=IF(D2<>"",C2,"")
Step 3
Sort column E from A to Z, or column D from Z to A, to move the duplicate values to the top.
Dec 01 2017 08:51 AM - edited Dec 01 2017 08:52 AM
Hello Haytham,
I applied your formula on a new spread sheet but I am getting a blank result. How do I run the formula? This is what I have so far:
=IF(ISNUMBER(MATCH(B2,$A$2:$A$1538,0)),B2,"") In Column D. I just hit Enter and nothing came out.
=IF(D2<>"",C2,"") In Column E. I also hit enter and nothing came out.
Dec 01 2017 10:09 AM
After you hit enter drag each formula down by using the fill handle.
Then sort column E from A to Z, or column D from Z to A, to move the duplicate values to the top.
Dec 01 2017 01:07 PM
Okay, I have to hard type the formula and not just copy your formula and paste it. Also, I dragged each formula down and I got the result which is cool. The only problem I am having now is when I'm sorting it, either in Column E or D, the dates are off? I am comparing the dates from the original and somehow some dates are showing up on ID 2 where there shouldn't be any date at all.
For example:
ID 2 (column 2) one ID has no date next to it, but after doing the sorting in column E (dates), the ID with no date will have a date. I think I will not sort it for now until I get more help from you. So far, I got the result that I need. It's the sorting now that I am having problem with, thanks.
Dec 01 2017 08:15 PM - edited Dec 01 2017 08:18 PM
I guess that the date that you got is something like this: 01/00/1900
If you have blank cells in the column C (DATE column), then replace the below formula in column E:
=IF(D2<>"",C2,"")
With this:
=IF(C2<>"",IF(D2<>"",C2,""),"")
After that, sort column D from Z to A.
Dec 05 2017 11:21 AM
This time it worked! Thank you so much.
I have another question. In case I need to add another column for let say Serial Number (sn) which that serial numbers belongs to column B, how will I include that new column info? I know that in column B and C we incorporated that result in D & E, now just in case I need to insert another column let's say insert it next to B (I know all the columns will adjust to the right, if I insert a column after the B column). I need the B, C, D columns result to be together in another column just like what we did in B & C which the result went to D & E. I assume this time, I'll have column D, E, F for the results. Not sure if I'm clear with my question, but my point is column B originally has more info such as serial number, site delivered date, etc. attached to it. In case I have to insert another column, what formula should we use?
Thank you in advance.
Dec 05 2017 08:26 PM - edited Dec 05 2017 08:32 PM
You don't need to any formula!
To insert a blank column between adjacent columns contain data, select the entire column, and click insert as shown in the below screenshots:
The data in column C, D and E will move to the right, and the formulas that were in columns D and E its results won't change!
After that, add the data that you want in the new blank column.
Dec 06 2017 09:16 AM - edited Dec 06 2017 09:47 AM
I think what I want is that the new inserted column will also appear on the last column G. The same concept that we did in column D & E before (these columns are now E & F because I inserted a column (c), but this time will have 3 results in ( E, F, G) G would be the serial number that came from column C. I attached an example. So I think we need a formula in column G for the SN-ID2 in column C to appear in column G. These are the formula that we have so far:
Column E
=IF(ISNUMBER(MATCH(B2,$A$2:$A$2067,0)),B2,"")
Column F
=IF(D2<>"",IF(E2<>"",D2,""),"")
Column G?
Thanks.
Dec 07 2017 07:28 AM
Please find the solution in the attachment file.
Dec 07 2017 03:16 PM
Great, thank you! I got want I needed. I am having just one more issue, the sorting. I was trying to sort the column F (dates) from Oldest to Newest, but every time I hit enter the columns E, F, and G are disappearing? Am I doing the correct sorting?
Dec 07 2017 08:29 PM
They didn't disappear, but moved down and dispersed.
To move them back to the top, sort column A (ID1) from A to Z, or Column E (Duplicate ID) from Z to A.
Dec 08 2017 09:06 AM
I was able to sort them this time without the other columns disappearing. I also used Notepad ++ to fix the blank spaces then recopied it back to Excel for my reports.
Thank you so much for your help.
Nov 30 2017 09:21 PM - edited Nov 30 2017 09:22 PM
SolutionArnold,
Step 1
Put this formula in cell D2 and fill it down:
=IF(ISNUMBER(MATCH(B2,$A$2:$A$200,0)),B2,"")
Don't forget to change this range $A$2:$A$200 to your existing range.
Step 2
Put this formula in cell E2 and fill it down:
=IF(D2<>"",C2,"")
Step 3
Sort column E from A to Z, or column D from Z to A, to move the duplicate values to the top.