College Project Excel Help

[Jktuchman]

I have to sort through over a million cells of data. I have two sources I had to get this data from and combine it into on excel sheet. One of the data has a list of all the airplanes that are currently owned in the US. Included is the address where the airplane owner resides. The name of the owner is not on this list. The other data sheet has a list of the active pilots in the US. Included is their full name and address where they reside. My goal is to match the pilots with their planes. To do that I combined both sources of data into one excel sheet and then sorted it based on address. This gave me a very long list of all the pilot and plane addresses next to each other. It is now my job to sort through it and highlight the plane and pilot whose address match each other. From there I can filter the data based on color and have a list of all the pilots who own planes. This takes very long. Is there a better way to do this?

Please note things I have tried:
I've tried to use "conditional formatting" -> "Highlighting Cell Rules" -> "Duplicate Values"
This worked but only picked up on the duplicate value from the same imported worksheet. It didn't pick up on the two cells from the two different imported worksheets of pilot and airplanes. I've tried to reformat all the cells to be the same and then repeat the above steps but it still didn't work.

Thank you for your time,

Joe

 

[Somendra Misra]

Hi Joe,

Welcome to the forum.

It will be good if you can upload a sample file with say 100 lines of data in it.

Regards,

 

[NARAYANK991]

Hi ,

First , if you have two workbooks to start with , we can work with the two of them ; there is no need to combine them and then work with the resulting one workbook , especially if it means additional data processing.

If you can upload samples of both the workbooks , that would be nice , especially if your data is in the form of Excel workbooks.

Narayan

 

[Jktuchman]

Hi Joe,

Welcome to the forum.

It will be good if you can upload a sample file with say 100 lines of data in it.

Regards,

Thank you Somendra,

Attached is a sample of the excel sheet. Highlighted in the excel sheet is what I manually highlighted myself. I would need to automate that process for the entire work book.

Kind Regards,

Joe

 

[Jktuchman]

Hi ,

First , if you have two workbooks to start with , we can work with the two of them ; there is no need to combine them and then work with the resulting one workbook , especially if it means additional data processing.

If you can upload samples of both the workbooks , that would be nice , especially if your data is in the form of Excel workbooks.

Narayan

Narayan,

Attached are the two separate workbooks which I have shortened due to upload size.

Kind Regards,

Joe

 

[NARAYANK991]

Hi Joe ,

The second file appears to be somewhat unstructured ; there are no column headers for the data , and in rows 4 , 6 , 10 , 12 and 17 , the data in one column appears to offset , and differently structured.

Do we have to work with this file , or can something be done to make it structured ?

The first file is well laid out , and working with it should be easy , especially if the data is converted to a table , as in the attached file. I suggest you start using tables , if you have Excel version 2007 or later.

Narayan

 

[Jktuchman]

Hi Joe ,

The second file appears to be somewhat unstructured ; there are no column headers for the data , and in rows 4 , 6 , 10 , 12 and 17 , the data in one column appears to offset , and differently structured.

Do we have to work with this file , or can something be done to make it structured ?

The first file is well laid out , and working with it should be easy , especially if the data is converted to a table , as in the attached file. I suggest you start using tables , if you have Excel version 2007 or later.

Narayan

Attached is another file that we can work with. Does this one help?

The file you attached looks great! I will start converting them to tables for easier use.

 

[NARAYANK991]

Hi Joe ,

Yes , this latest upload is much better to work with.

All you need to specify now is how do we match entries from the two workbooks ?

Which column in the your latest uploaded file corresponds to any particular column in the first file ?

Narayan

 

[Jktuchman]

Hi Joe ,

Yes , this latest upload is much better to work with.

All you need to specify now is how do we match entries from the two workbooks ?

Which column in the your latest uploaded file corresponds to any particular column in the first file ?

Narayan

Correct.

Column D in both uploads have corresponding fields.

 

[NARAYANK991]

Hi Joe ,

There does not seem to be any match !

Can you check the attached file ?

Narayan

 

[Jktuchman]

Hi Joe ,

There does not seem to be any match !

Can you check the attached file ?

Narayan

Sorry Narayan. I've attached two worksheets which I know have matching addresses. I'm not quite sure what happened to the first sample excel sheet I sent you. This new one has 6 matches. I apologize for the mistake.

 

[NARAYANK991]

Hi Joe ,

See the attached files.

Narayan

 

[Jktuchman]

Hi Joe ,

See the attached files.

Narayan

Thank you This is great! I have two questions:
1. On the file "Airmen and planes test 20" I know the input you created has found and highlighted all cells that have the same addresses, but will I be able to view the plane in this file as well? If not, not a problem at all.
2. How do I replicate this for the entire data sheet I have? Did you import this into a table and sort the data that way?

 

[NARAYANK991]

Hi Joe ,

In both the workbooks , there are formulae in column T ; this is because the highlighting is being done using Conditional Formatting , and this does not allow the CF formulae to be in external workbooks ; thus a helper column has been used in each workbook which refers to the other workbook and the CF rule now makes use of the results in column T.

If you want to replicate this , just use the same technique ; if your data is not in the form of tables , you will have to rewrite the formulae to use cell references instead of table nomenclature.

Narayan

 

[Jktuchman]

Hi Joe ,

In both the workbooks , there are formulae in column T ; this is because the highlighting is being done using Conditional Formatting , and this does not allow the CF formulae to be in external workbooks ; thus a helper column has been used in each workbook which refers to the other workbook and the CF rule now makes use of the results in column T.

If you want to replicate this , just use the same technique ; if your data is not in the form of tables , you will have to rewrite the formulae to use cell references instead of table nomenclature.

Narayan

How does this look? Attached are the two files I did myself. You need both to be open in order for it to work. How can I get the True to highlight?

Kind Regards,

Joe

 

[NARAYANK991]

Hi Joe ,

The second file does not need to be open for the formula to work.

However , I don't find any matches ; are you able to see any match which the formula is not returning ?

Narayan

 

[Jktuchman]

I have it set up so you need both excel files to be open. I tried to put the formula in the other excel file but I couldn't get it to work.

 

[NARAYANK991]

Hi Joe ,

Can you open the files I have uploaded in my previous post ?

The formulae are in both files ; they are not finding any matches.

Narayan

 

[Jktuchman]

I know why. I took the excel file and saved it as another shorter file and sent it to you. If you look at the formula in the excel sheet it is different than the names of the saved file itself.