Display common values in column A & B of sheet 1 & sheet 2 in excel

excelformulahelp

New member
Joined
Jul 4, 2018
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
Hi sir/Madam,

I am really struggling to compare and display the values in excel.

My requirement: I have excel sheet with two tabs. First tab has two columns A and B. A is having values of incident id and B is having corresponding dates. In same way Second tab also has two columns A and B . A is having values of incident id and B is having corresponding dates. Some values of column A of first tab has matches in second tab column A. So I want to display those matched values of Column A of first and second tabs and their (column A's) corresponding values from column B of both tabs in separate tab in the format as below tableView attachment excel.xlsx. I have highlighted I have attached excel spreadsheet. I am really in need of this. It would be appreciated if someone help me on this. Thanks in advance
Display values common in Column A of First and second tabs (Highlighted in yellow)Display the column A’s corresponding value in column B of First tab (Highlighted in yellow)Display the column A’s corresponding value in column B of Second tab (Highlighted in yellow)
INC0000182083044/30/2018 16:305/1/18 2:00
INC0000182082914/30/2018 16:245/1/18 1:54
 
Last edited by a moderator:
Please explain me few points:
1.would you like display for all the IDs or only those which are common in both the sheets?
2.which is primary sheet, first or second
3."first sheet" has some IDs that has been used more than one in the same sheet. so which time would u like to display, like earliest time/latest time,other.
4. Can there be any ID in "second sheet" not included in "first sheet" as I find none.
 
Please explain me few points:
1.would you like display for all the IDs or only those which are common in both the sheets? - My Answer):-I want to display only those which are common in both the sheets and corresponding B column values of those common Ids from both sheets

2.which is primary sheet, first or second - My Answer):- You can consider any sheet as primary. I assume no importance of primary here.

3."first sheet" has some IDs that has been used more than one in the same sheet. so which time would u like to display, like earliest time/latest time,other. - There are duplicates in first sheet. Please do not think of duplicates. You can keep duplicates as it is. I like to display, like latest time.

4. Can there be any ID in "second sheet" not included in "first sheet" as I find none.
- Second sheet has more ids and out of them few ids are not there in first sheet.


I am showing some of the result here how it to be displayed:


common in Column A of First and second tabs (Highlighted in yellow in attached excel)
respective Column B value of first tab (Highlighted in yellow in attached excel) respective Column B value of second tab(Highlighted in yellow in attached excel)
INC000018208304

4/30/2018 16:30

5/1/18 2:00

INC000018208291

4/30/2018 16:24

5/1/18 1:54



Please check attached excel.
 

Attachments

  • excel.xlsx
    25.3 KB · Views: 3
I have fine tuned my query now so that you could understand easily and I am posting it again now. Thanks


I have highlighted the common ids and their date values in yellow color in sheet1& 2. In another sheet, I have copied expected result manually. Instead of manual work, I need some formula. In the expect result should have common ids of sheet1, 2 in Column A and their dates in Column B in sheet 1, 2.


Please refer attached excel speardsheet to understand better. Could you please assist me in finding formula to complete this task. Thanks in advance
 

Attachments

  • exceldata.xlsx
    24.6 KB · Views: 10
When your thread title appears in the search engines it won't attract much attention depriving you from valuable help.
 
I am attaching a excel file. Let me know if it works

Few points to consider:
1. My assumption: second sheet is the main sheet.
2. IDs in second sheet will always be present in sheet 1. (In your working there are 172 entries in sheet 2 and all are present in sheet 1.)
3. In case of increase of Ids, please ensure to copy paste the formula in sheet ExpectedREsult

In case if you need expectedresult of selected IDs( in sheet 2) , the working will be a whole lot different.
 

Attachments

  • exceldata.xlsx
    37.8 KB · Views: 11
Hi Sahil,

It seems this looks good, but I am verifying excel manually. In sheet 2 , Columns D,E have values. What are those values? It has to be there?
 
Sahil,

Ignore if solved, just saw this 10 minutes ago.

Not sure what your end game is with this data so I have given you basic Consol and added a few extra columns in Consol Plus. Both being power Queried and have added some slicers on consol table and a few pivot tables with slicers on Consol Plus.



Consol - Table is 1, Slicers are 2/3 https://www.screencast.com/t/euw7yghjg1hw

Consol Plus Table 1 with extra cols from original data set, 2/3 pivot tables different perspectives and slicers 4 BMC slicers, 5 BO slicers, 6 removes the filters. https://www.screencast.com/t/g4D3bNY9gp2

Hopefully has not complicated your life, if it has then just focus on the table on Consol sheet.

Knock 'em dead Sailor!
 

Attachments

  • exceldata.xlsx
    89.1 KB · Views: 5
Hi Sahil,

It seems this looks good, but I am verifying excel manually. In sheet 2 , Columns D,E have values. What are those values? It has to be there?

You can ignore and delete those columns.


Attaching new file.
 

Attachments

  • exceldata.xlsx
    34 KB · Views: 7
Back
Top