DATA across 3 columns and output to 4th

Dear all,

I have a certain Excel workbook which has data stored in 3 columns. Column A has around 300 entries, column B has around 980 and similar for column C. There are certain similar entries between columns A and B.

What I wish to do is to find all the similar entries between A & B, then match it to the corresponding entry in column C and then give a output in the same worksheet

I'm attaching the sample for easier understanding and analysis

Any guidance would be appreciated, solving this basically finishes my entire project

What do you mean by:

... certain similar entries between columns A and B ...

If they were exactly the same then you should be able to use this formula in E2:

=IF(COUNTIF(B:B,A2)>0,VLOOKUP(A2,B:C,2,0),"")

and copy this down to E300, but I don't see anything returned so the values can't be exactly the same.

Pete

I'm sorry alansidman and Pete_UK for my absence and delayed reply

The issue is exactly that...the number of values in A and B isn't the same and hence I turned to the experts here

To illustrate

The value in A3 and B4 is the same and the corresponding value is C4

Basically I want such values to be filtered and displayed

A3 is not the same as B4. You can check this by putting this formula in an empty cell:

=B4=A3

and it will return FALSE. The problem is that you have a <new-line> character (with a code of 10) at the end of most of those entries in column A, which does not appear in column B, so there is no exact match. You can either remove them from column A, or include them within the formula.

To remove them from column A in one operation (the preferable option), you should highlight the data in column A, then CTRL-H (for Find & Replace), and:

Find What: Alt-0010
Replace With: leave blank

then click Replace All, where Alt-0010 means holding down the Alt key and typing 0010 on the numeric keypad - or you could do CTRL-J in that field. Then you can use the formula that I gave you yesterday in E2, copied down to E300.

If you want to get rid of them by formula, you need to put this formula in E2:

=IF(COUNTIF(B:B,SUBSTITUTE(A2,CHAR(10),""))>0,VLOOKUP(SUBSTITUTE(A2,CHAR(10),""),B:C,2,0),"")

then copy it down to E300.

Hope this helps.

Pete

Thank you for your reply

The find and replace option isn't working for me..either with Alt+0010 or Ctrl-J..it says not found

And the formula is giving me some data which is looking like data in column C...I thought the output will be same like column A minus that extra code 10 line

To be clear what I want is the similar entries in A and B to be displayed in column D or E or whatever and the matching values taken from column C to be displayed adjacent to column D or E whichever may be the case

Here is the file with the formula-solution in.

Hope this helps.

Pete

Thank you so much kind sir

Is there any way I can see the common values from A and B in column E and the corresponding values in C displayed in column F

Also please tell me how to replicate this as I have 4-5 similar files

If you want them all bunched up with no gaps between them, then see attached file, which has formulae in D2, E2 and F2, with appropriate headings in row 1. These formulae should be copied down to the bottom of your data in column A (i.e. to row 300) - there is no need to copy them any further, but there is no harm if you do.

To replicate this in other files, just copy/paste D1:F2 across to the other file, then copy the formulae down.

Hope this helps.

Pete

Thanks a lot kind sir

I was able to successfully follow your instructions and replicate it in my other sheets

I'm attaching the file you sent with the formulas in D1:F2 along with the data for 2013 and 2014 which I managed from your instructions.

Now I have two questions

1) How do I copy the data from columns E and F into a new excel sheet? I tried but it turns out to be blank. I understand it is under some formula and hence turning blank into another sheet.

But how do I copy just the plain data in E and F?

2) How do I arrange the data in column E in numerical order and R in the end coming first before L (and also automatically make the changes in F)

Example

PEB/002/01/12/R
PEB/002/01/12/L
PEB/008/01/12/R
PEB/008/01/12/L

and so forth

I've done the copy/paste and sorting as per your formulas

What struck me here is the counting done by Excel

Example

In the '2012 sorted' sheet, the count for columns A and B is shown to be 300 each but visual inspection shows it is 235 each for both

Similarly 333 shown in 2013, but it is 271

417 shown in 2014, but it is 344

Why this disparity and how to correct it?

Position your cursor in A1 of one of the sorted sheets, then press <End> followed by <down-arrow>, and it will take you way beyond where your data stops. This is because the original formulae in the sheets 2012 etc. were copied to the bottom of the data in column A (i.e. to row 300 in 2012 sheet), but there were only 235 matching items of data, which got bunched up in columns E and F. However, there were still formulae in those lower cells which were returning blanks, so when you copy/pasted the values from those formulae into the sorted sheets, it will have copied the "formula-blanks" into the lower cells. These are not the same as a cell being empty, and the COUNTA function will include them.

It you want to get rid of them completely, then you can just highlight all the "blank" rows below where your data finishes in the sorted sheets, and click on Delete, then save the file.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

Thank you sir

Attaching the sorted out file..looks mostly okay

Thanks for all your timely help and guidance

You're welcome, and glad that you got your result.

Please mark the thread as SOLVED if you think it is.

Pete

If I mark it as solved and some days later, realize some issue with the same file then can I reopen or have to post a new thread?

You can do either.

It may be better to start a new thread, though, with a link back to this if you think it is necessary, and then new eyes will look at your new thread (some contributors are reluctant to get involved in a long thread that someone else has been helping with).

Pete

Ok sir

Thanks once again