Two different tables - 4 columns each

NElmer

New Member
Joined
Jul 12, 2022
Messages
16
Office Version
  1. 365
Platform
  1. Windows
How can I merge two different tables that each have 4 columns one of the columns in each table contain a policy number. The other 3 columns have values in them, as well as some blank cells.

I need to merge/combine the two tables so that when sorted by policy number (lowest to highest) the other 3 columns will fall into place as they should either showing the correlating value or blank cell related to that policy number.

Example: merge A:D with G:J to produce new table starting at M:P with data from B:D and H:J into N:P respectively.

Thank you!
 

Attachments

  • Screenshot_20230822_140354_Excel.jpg
    Screenshot_20230822_140354_Excel.jpg
    62.8 KB · Views: 16

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
How about
Excel Formula:
=sort(vstack(a2:d8,g2:j7))
 
Upvote 0
Hello, thank you for quick reply. That produces a #NUM! error. However, I didn't format the data as tables yet- would that make any difference?
 
Upvote 0
Nope, that won't matter.
Is the error in some cells?
 
Upvote 0
I may have figured it out! I think it was the lack of tables that was causing the issue. However, it is not intertwining the values it is just now 1 table on top of the other
 
Upvote 0
Just saw you reply about error on cells. Some do have that little triangle is the corner bc I have it a general format. I will mark to ignore those and see if that helps
 
Upvote 0
Can you post some sample data
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
One more question. (Forgot to add this part in my inital post and example) the second table will have some duplicate policy numbers that are also in the other column. If there a version of this formula that can tell it to ignore the duplicate policy numbers on the second table?
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top