Forum Discussion
Solved
Excel formula help
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. 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) |
INC000018208304
| 4/30/2018 16:30
| 5/1/18 2:00
|
INC000018208291
| 4/30/2018 16:24
| 5/1/18 1:54
|
Hi, to all!
Check file with an option. Blessings!
8 Replies
It looks like the question is duplicated
Really Thanks for your valuable assistance.
- you can use VLOOKUP to bring the values from column B of both the sheets. the formula will throw an error when it cant find match for the given incident number in the second sheet which we will handle by using IFERROR and returning the string "No match" in case of error. so the formula for column B in sampleresultsheet will be:
=VLOOKUP(A2,'First Sheet'!$A$2:$B$391,2,0)
and the formula for column C will be:
=IFERROR(VLOOKUP(A2,'Second sheet'!$A$2:$B$173,2,0),"No match")- Dear Takmil, Really Thanks for your valuable assistance.
Hi Takmil,
Really thanks to you for sharing formula. But I have tested them and not working. I have fine tuned my query and posting it here again so that you could understand easily.
Sheet1 has two columns A,B. Column A has ids and column B has dates of these respective ids.
Sheet2 has two columns A,B. Column A has ids and column B has dates of these respective ids.
Few ids in sheet1 and sheet2 are common/same but dates are different. So I want to display those common ids and their dates of sheet1 & 2 in sheet3. Here is sheet3 is expected result. Here, there is no option to attach excel file, hence I have copied sheet1,2 and sheet3(expected result) as below:
Please refer below excel sheets so that you can understand easily my requirement.
Below expected result should be displayed in excel sheet3. I have did this result manually as I do not know how to write formula. I request you to write formula and assist me. Thanks in advance
You can refer attached excel as well. Excel also is having same as per below.
Expected Result as below: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 INC000018208287 4/30/2018 16:23 5/1/18 1:53 INC000018208276 4/30/2018 16:20 5/1/18 1:50 INC000018208272 4/30/2018 16:19 5/1/18 1:49 INC000018208269 4/30/2018 16:18 5/1/18 1:48 INC000018208268 4/30/2018 16:17 5/1/18 1:47 INC000018208267 4/30/2018 16:15 5/1/18 1:45 INC000018208264 4/30/2018 16:14 5/1/18 1:44 INC000018208260 4/30/2018 16:12 5/1/18 1:42 INC000018208258 4/30/2018 16:11 5/1/18 1:41 INC000018208257 4/30/2018 16:10 5/1/18 1:40 INC000018208251 4/30/2018 16:08 5/1/18 1:38 INC000018208243 4/30/2018 16:06 5/1/18 1:36 INC000018208242 4/30/2018 16:05 5/1/18 1:35 INC000018208235 4/30/2018 16:03 5/1/18 1:33 INC000018208231 4/30/2018 16:02 5/1/18 1:32 INC000018208228 4/30/2018 16:00 5/1/18 1:30 INC000018208225 4/30/2018 15:59 5/1/18 1:29
Sheet1:
COLUMN A COLUMN BINC000018210604 5/1/2018 16:37 INC000018210602 5/1/2018 16:35 INC000018210596 5/1/2018 16:33 INC000018210590 5/1/2018 16:29 INC000018210587 5/1/2018 16:27 INC000018210583 5/1/2018 16:26 INC000018210578 5/1/2018 16:24 INC000018210570 5/1/2018 16:17 INC000018210565 5/1/2018 16:15 INC000018210563 5/1/2018 16:14 INC000018209826 5/1/2018 11:11 INC000018209641 5/1/2018 10:14 INC000018209619 5/1/2018 10:06 INC000018208304 4/30/2018 16:30 INC000018208293 4/30/2018 16:25 INC000018208291 4/30/2018 16:24 INC000018208287 4/30/2018 16:23 INC000018208276 4/30/2018 16:20 INC000018208272 4/30/2018 16:19 INC000018208269 4/30/2018 16:18 INC000018208268 4/30/2018 16:17 INC000018208267 4/30/2018 16:15 INC000018208264 4/30/2018 16:14 INC000018208260 4/30/2018 16:12 INC000018208258 4/30/2018 16:11 INC000018208257 4/30/2018 16:10 INC000018208251 4/30/2018 16:08 INC000018208243 4/30/2018 16:06 INC000018208242 4/30/2018 16:05 INC000018208235 4/30/2018 16:03 INC000018208231 4/30/2018 16:02 INC000018208228 4/30/2018 16:00 INC000018208225 4/30/2018 15:59 INC000018208221 4/30/2018 15:58 INC000018208219 4/30/2018 15:56 INC000018208216 4/30/2018 15:54 INC000018208214 4/30/2018 15:53 INC000018208208 4/30/2018 15:52 INC000018208204 4/30/2018 15:50 INC000018208198 4/30/2018 15:48 INC000018208193 4/30/2018 15:47 INC000018208187 4/30/2018 15:45 INC000018208184 4/30/2018 15:44 INC000018208177 4/30/2018 15:42 INC000018208175 4/30/2018 15:40 INC000018208171 4/30/2018 15:38 INC000018208165 4/30/2018 15:37 INC000018208161 4/30/2018 15:35 INC000018208158 4/30/2018 15:34 INC000018208154 4/30/2018 15:32 INC000018208149 4/30/2018 15:30 INC000018208146 4/30/2018 15:29 INC000018208143 4/30/2018 15:27 INC000018208139 4/30/2018 15:26 INC000018208131 4/30/2018 15:24 INC000018208128 4/30/2018 15:22 INC000018208123 4/30/2018 15:20 INC000018208116 4/30/2018 15:19 INC000018208112 4/30/2018 15:18 INC000018208102 4/30/2018 15:16 INC000018208092 4/30/2018 15:14 INC000018208088 4/30/2018 15:13 INC000018208082 4/30/2018 15:11 INC000018208072 4/30/2018 15:07 INC000018208049 4/30/2018 14:56 INC000018208044 4/30/2018 14:54 INC000018208008 4/30/2018 14:30 INC000018208004 4/30/2018 14:28 INC000018207709 4/30/2018 12:34 INC000018207701 4/30/2018 12:32 INC000018207570 4/30/2018 11:48 INC000018206593 4/30/2018 7:00 INC000018202389 4/27/2018 16:10 INC000018202077 4/27/2018 13:54 INC000018201971 4/27/2018 13:17 INC000018201968 4/27/2018 13:15 INC000018201963 4/27/2018 13:13 INC000018201114 4/27/2018 8:40 INC000018200596 4/27/2018 5:00 INC000018199738 4/26/2018 16:47 INC000018199657 4/26/2018 16:05 INC000018198364 4/26/2018 8:47 INC000018198362 4/26/2018 8:46 INC000018198359 4/26/2018 8:46 INC000018198357 4/26/2018 8:44 INC000018198352 4/26/2018 8:42 INC000018197422 4/26/2018 3:05 INC000018197397 4/26/2018 2:54 INC000018197381 4/26/2018 2:45 INC000018197354 4/26/2018 2:33 INC000018195291 4/25/2018 9:37 INC000018194742 4/25/2018 6:49 INC000018194737 4/25/2018 6:47 INC000018194735 4/25/2018 6:46 INC000018194732 4/25/2018 6:44 INC000018194728 4/25/2018 6:43 INC000018194171 4/25/2018 2:42 INC000018193464 4/24/2018 16:02 INC000018193189 4/24/2018 13:54 INC000018193173 4/24/2018 13:49 INC000018193135 4/24/2018 13:37 INC000018192810 4/24/2018 11:43 INC000018192806 4/24/2018 11:41 INC000018192799 4/24/2018 11:39 INC000018192772 4/24/2018 11:29 INC000018192768 4/24/2018 11:29 INC000018192395 4/24/2018 10:01 INC000018192292 4/24/2018 9:37 INC000018192282 4/24/2018 9:34 INC000018191401 4/24/2018 4:35 INC000018190117 4/23/2018 15:04 INC000018189835 4/23/2018 13:18 INC000018189753 4/23/2018 13:00 INC000018189039 4/23/2018 9:15 INC000018188495 4/23/2018 6:42 INC000018188420 4/23/2018 6:07 INC000018185182 4/21/2018 13:55 INC000018185174 4/21/2018 13:44 INC000018185171 4/21/2018 13:39 INC000018184365 4/20/2018 17:02 INC000018184221 4/20/2018 15:15 INC000018182382 4/20/2018 0:59 INC000018181799 4/19/2018 15:48 INC000018181555 4/19/2018 13:50 INC000018181246 4/19/2018 12:06 INC000018180931 4/19/2018 10:52 INC000018180805 4/19/2018 10:25 INC000018180652 4/19/2018 9:47 INC000018180491 4/19/2018 9:06 INC000018180327 4/19/2018 8:24 INC000018179797 4/19/2018 4:25 INC000018179678 4/19/2018 3:24 INC000018179498 4/19/2018 1:48 INC000018178702 4/18/2018 15:57 INC000018178543 4/18/2018 14:49 INC000018177516 4/18/2018 9:24 INC000018177313 4/18/2018 8:41 INC000018176724 4/18/2018 5:00 INC000018176601 4/18/2018 4:04 INC000018176086 4/18/2018 1:38 INC000018175480 4/17/2018 16:02 INC000018175472 4/17/2018 16:01 INC000018175468 4/17/2018 16:00 INC000018175466 4/17/2018 15:59 INC000018174185 4/17/2018 8:57 INC000018174111 4/17/2018 8:37 INC000018174058 4/17/2018 8:15 INC000018174052 4/17/2018 8:13 INC000018173970 4/17/2018 7:52 INC000018173059 4/17/2018 0:51 INC000018172473 4/16/2018 15:56 INC000018172131 4/16/2018 13:41 INC000018171781 4/16/2018 11:44 INC000018171479 4/16/2018 10:27 INC000018171250 4/16/2018 9:30 INC000018165184 4/13/2018 20:24 INC000018165010 4/13/2018 17:07 INC000018163972 4/13/2018 9:49 INC000018163912 4/13/2018 9:29 INC000018163759 4/13/2018 8:45 INC000018163747 4/13/2018 8:42 INC000018163638 4/13/2018 8:18 INC000018162822 4/13/2018 1:52 INC000018162814 4/13/2018 1:47 INC000018162809 4/13/2018 1:43 INC000018162368 4/12/2018 17:15 INC000018162365 4/12/2018 17:09 INC000018161757 4/12/2018 12:30 INC000018160850 4/12/2018 7:55 INC000018160845 4/12/2018 7:52 INC000018160843 4/12/2018 7:50 INC000018159404 4/11/2018 15:54 INC000018159366 4/11/2018 15:37 INC000018159311 4/11/2018 15:11 INC000018159043 4/11/2018 13:17 INC000018158895 4/11/2018 12:23 INC000018158890 4/11/2018 12:22 INC000018158498 4/11/2018 10:26 INC000018158220 4/11/2018 9:16 INC000018158160 4/11/2018 9:00 INC000018158112 4/11/2018 8:46 INC000018158106 4/11/2018 8:45 INC000018158099 4/11/2018 8:44 INC000018158093 4/11/2018 8:42 INC000018158080 4/11/2018 8:40 INC000018157165 4/11/2018 2:30 INC000018157140 4/11/2018 2:12 INC000018156637 4/10/2018 17:14 INC000018156602 4/10/2018 16:57 INC000018156517 4/10/2018 15:52 INC000018156259 4/10/2018 14:06 INC000018156203 4/10/2018 13:47 INC000018155707 4/10/2018 11:07 INC000018155664 4/10/2018 10:54 INC000018155451 4/10/2018 9:49 INC000018155014 4/10/2018 7:40 INC000018155007 4/10/2018 7:38 INC000018155002 4/10/2018 7:36 INC000018154638 4/10/2018 5:05 INC000018154516 4/10/2018 4:09 INC000018154514 4/10/2018 4:08 INC000018154509 4/10/2018 4:06 INC000018153493 4/9/2018 15:15 INC000018153490 4/9/2018 15:14 INC000018153487 4/9/2018 15:13 INC000018153315 4/9/2018 13:59 INC000018153142 4/9/2018 13:01 INC000018153139 4/9/2018 13:00 INC000018152590 4/9/2018 12:44 INC000018152422 4/9/2018 11:43 INC000018152417 4/9/2018 11:40 INC000018152408 4/9/2018 11:37 INC000018152398 4/9/2018 11:35 INC000018152370 4/9/2018 11:28 INC000018151443 4/9/2018 8:17 INC000018151001 4/9/2018 5:33 INC000018147070 4/6/2018 14:06 INC000018147041 4/6/2018 13:59 INC000018147036 4/6/2018 13:57 INC000018147034 4/6/2018 13:57 INC000018147028 4/6/2018 13:55 INC000018146421 4/6/2018 10:21 INC000018146413 4/6/2018 10:20 INC000018146405 4/6/2018 10:18 INC000018146395 4/6/2018 10:16 INC000018146389 4/6/2018 10:13 INC000018146344 4/6/2018 10:01 INC000018146323 4/6/2018 9:53 INC000018146255 4/6/2018 9:33 INC000018145654 4/6/2018 5:24 INC000018145636 4/6/2018 5:19 INC000018145621 4/6/2018 5:14 INC000018144691 4/5/2018 16:47 INC000018144474 4/5/2018 14:58 INC000018144379 4/5/2018 14:25 INC000018144375 4/5/2018 14:24 INC000018144372 4/5/2018 14:23 INC000018144366 4/5/2018 14:22 INC000018144362 4/5/2018 14:20 INC000018144024 4/5/2018 12:21 INC000018144023 4/5/2018 12:19 INC000018144021 4/5/2018 12:18 INC000018144016 4/5/2018 12:17 INC000018144009 4/5/2018 12:14 INC000018144002 4/5/2018 12:09 INC000018143996 4/5/2018 12:08 INC000018143742 4/5/2018 10:51 INC000018143731 4/5/2018 10:49 INC000018143728 4/5/2018 10:48 INC000018143719 4/5/2018 10:46 INC000018143700 4/5/2018 10:41 INC000018143691 4/5/2018 10:39 INC000018143683 4/5/2018 10:37 INC000018142619 4/5/2018 5:11 INC000018141712 4/4/2018 16:56 INC000018141710 4/4/2018 16:54 INC000018141709 4/4/2018 16:53 INC000018141707 4/4/2018 16:52 INC000018141705 4/4/2018 16:51 INC000018141555 4/4/2018 15:46 INC000018141543 4/4/2018 15:38 INC000018141345 4/4/2018 14:50 INC000018141031 4/4/2018 13:48 INC000018141010 4/4/2018 13:43 INC000018140791 4/4/2018 12:44 INC000018140782 4/4/2018 12:41 INC000018140773 4/4/2018 12:39 INC000018140413 4/4/2018 11:03 INC000018139382 4/4/2018 6:12 INC000018138051 4/3/2018 15:39 INC000018137930 4/3/2018 14:50 INC000018137926 4/3/2018 14:50 INC000018137921 4/3/2018 14:48 INC000018136682 4/3/2018 8:45 INC000018136327 4/3/2018 7:01 INC000018135542 4/2/2018 23:59 INC000018135527 4/2/2018 23:47 INC000018135317 4/2/2018 19:08 INC000018135316 4/2/2018 19:06 INC000018135161 4/2/2018 15:06 INC000018134512 4/2/2018 6:58 INC000018133792 4/1/2018 21:45 INC000018133790 4/1/2018 21:43 INC000018133786 4/1/2018 21:39 INC000018133784 4/1/2018 21:38 INC000018131272 3/30/2018 18:55 INC000018131255 3/30/2018 17:55 INC000018131180 3/30/2018 15:44 INC000018131179 3/30/2018 15:41 INC000018131178 3/30/2018 15:39 INC000018131177 3/30/2018 15:37 INC000018131176 3/30/2018 15:32 INC000018131173 3/30/2018 15:29 INC000018130797 3/30/2018 6:51 INC000018130603 3/30/2018 4:08
Sheet2:
COLUMN A COLUMN BINC000018209826 5/1/18 20:41 INC000018209641 5/1/18 19:44 INC000018209619 5/1/18 19:36 INC000018208304 5/1/18 2:00 INC000018208291 5/1/18 1:54 INC000018208287 5/1/18 1:53 INC000018208276 5/1/18 1:50 INC000018208272 5/1/18 1:49 INC000018208269 5/1/18 1:48 INC000018208268 5/1/18 1:47 INC000018208267 5/1/18 1:45 INC000018208264 5/1/18 1:44 INC000018208260 5/1/18 1:42 INC000018208258 5/1/18 1:41 INC000018208257 5/1/18 1:40 INC000018208251 5/1/18 1:38 INC000018208243 5/1/18 1:36 INC000018208242 5/1/18 1:35 INC000018208235 5/1/18 1:33 INC000018208231 5/1/18 1:32 INC000018208228 5/1/18 1:30 INC000018208225 5/1/18 1:29 INC000018208221 5/1/18 1:28 INC000018208219 5/1/18 1:26 INC000018208216 5/1/18 1:24 INC000018208214 5/1/18 1:23 INC000018208208 5/1/18 1:22 INC000018208204 5/1/18 1:20 INC000018208198 5/1/18 1:18 INC000018208193 5/1/18 1:17 INC000018208187 5/1/18 1:15 INC000018208184 5/1/18 1:14 INC000018208177 5/1/18 1:12 INC000018208175 5/1/18 1:10 INC000018208171 5/1/18 1:08 INC000018208165 5/1/18 1:07 INC000018208161 5/1/18 1:05 INC000018208158 5/1/18 1:04 INC000018208154 5/1/18 1:02 INC000018208149 5/1/18 1:00 INC000018208146 5/1/18 0:59 INC000018208143 5/1/18 0:57 INC000018208139 5/1/18 0:56 INC000018208131 5/1/18 0:54 INC000018208128 5/1/18 0:52 INC000018208123 5/1/18 0:50 INC000018208116 5/1/18 0:49 INC000018208112 5/1/18 0:48 INC000018208102 5/1/18 0:46 INC000018208092 5/1/18 0:44 INC000018208088 5/1/18 0:43 INC000018208082 5/1/18 0:41 INC000018208072 5/1/18 0:37 INC000018208049 5/1/18 0:26 INC000018208044 5/1/18 0:24 INC000018208008 5/1/18 0:00 INC000018208004 4/30/18 23:58 INC000018207709 4/30/18 22:04 INC000018207701 4/30/18 22:02 INC000018207570 4/30/18 21:18 INC000018206593 4/30/18 16:30 INC000018202389 4/28/18 1:40 INC000018202077 4/27/18 23:24 INC000018201971 4/27/18 22:47 INC000018201968 4/27/18 22:45 INC000018201963 4/27/18 22:43 INC000018201114 4/27/18 18:10 INC000018198364 4/26/18 18:17 INC000018198362 4/26/18 18:16 INC000018198359 4/26/18 18:16 INC000018198357 4/26/18 18:14 INC000018198352 4/26/18 18:12 INC000018195291 4/25/18 19:07 INC000018194737 4/25/18 16:17 INC000018194735 4/25/18 16:16 INC000018194732 4/25/18 16:14 INC000018194728 4/25/18 16:13 INC000018194171 4/25/18 12:12 INC000018193464 4/25/18 1:32 INC000018193135 4/24/18 23:07 INC000018192810 4/24/18 21:13 INC000018192806 4/24/18 21:11 INC000018192799 4/24/18 21:09 INC000018192772 4/24/18 20:59 INC000018192768 4/24/18 20:59 INC000018192395 4/24/18 19:31 INC000018192292 4/24/18 19:07 INC000018192282 4/24/18 19:04 INC000018191401 4/24/18 14:05 INC000018189835 4/23/18 22:48 INC000018189753 4/23/18 22:30 INC000018189039 4/23/18 18:45 INC000018188495 4/23/18 16:12 INC000018188420 4/23/18 15:37 INC000018185182 4/21/18 23:25 INC000018185171 4/21/18 23:09 INC000018184365 4/21/18 2:32 INC000018182382 4/20/18 10:29 INC000018181799 4/20/18 1:18 INC000018181555 4/19/18 23:20 INC000018181246 4/19/18 21:36 INC000018180931 4/19/18 20:22 INC000018180805 4/19/18 19:55 INC000018180652 4/19/18 19:17 INC000018180491 4/19/18 18:36 INC000018180327 4/19/18 17:54 INC000018179797 4/19/18 13:55 INC000018179498 4/19/18 11:18 INC000018178702 4/19/18 1:27 INC000018178543 4/19/18 0:19 INC000018177516 4/18/18 18:54 INC000018177313 4/18/18 18:11 INC000018176724 4/18/18 14:30 INC000018176601 4/18/18 13:34 INC000018176086 4/18/18 11:08 INC000018175480 4/18/18 1:32 INC000018175472 4/18/18 1:31 INC000018175468 4/18/18 1:30 INC000018175466 4/18/18 1:29 INC000018174111 4/17/18 18:07 INC000018174058 4/17/18 17:45 INC000018174052 4/17/18 17:43 INC000018173970 4/17/18 17:22 INC000018173059 4/17/18 10:21 INC000018172473 4/17/18 1:26 INC000018171781 4/16/18 21:14 INC000018171479 4/16/18 19:57 INC000018162809 4/13/18 11:13 INC000018159404 4/12/18 1:24 INC000018159366 4/12/18 1:07 INC000018159311 4/12/18 0:41 INC000018158895 4/11/18 21:53 INC000018158890 4/11/18 21:52 INC000018158498 4/11/18 19:56 INC000018158220 4/11/18 18:46 INC000018158160 4/11/18 18:30 INC000018158080 4/11/18 18:10 INC000018157140 4/11/18 11:42 INC000018156637 4/11/18 2:44 INC000018156259 4/10/18 23:36 INC000018156203 4/10/18 23:17 INC000018155707 4/10/18 20:37 INC000018155451 4/10/18 19:19 INC000018154638 4/10/18 14:35 INC000018154514 4/10/18 13:38 INC000018154509 4/10/18 13:36 INC000018153493 4/10/18 0:45 INC000018153487 4/10/18 0:43 INC000018153142 4/9/18 22:31 INC000018153139 4/9/18 22:30 INC000018152590 4/9/18 22:14 INC000018152422 4/9/18 21:13 INC000018152417 4/9/18 21:10 INC000018152408 4/9/18 21:07 INC000018152398 4/9/18 21:05 INC000018152370 4/9/18 20:58 INC000018151443 4/9/18 17:47 INC000018147034 4/6/18 23:27 INC000018145654 4/6/18 14:54 INC000018145636 4/6/18 14:49 INC000018145621 4/6/18 14:44 INC000018144691 4/6/18 2:17 INC000018143731 4/5/18 20:19 INC000018136682 4/3/18 18:15 INC000018134512 4/2/18 16:28 INC000018133790 4/2/18 7:13 INC000018133786 4/2/18 7:09 INC000018133784 4/2/18 7:08 INC000018131180 3/31/18 1:14 INC000018131179 3/31/18 1:11 INC000018131178 3/31/18 1:09 INC000018131176 3/31/18 1:02 Hi, to all!
Check file with an option. Blessings!
