Return Multiple Values From Two Columns In Two Separate Workbooks

DPXCV

New Member
Joined
Aug 16, 2024
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I have two workbooks that I am trying to match up multiple of the same sales orders numbers to different item numbers that fall under the same sales order number.

Column B in both workbooks contain the SO number, I would like to have a formula in column C of the second workbook that returns all the different item numbers in the first book, which is list under column C (see below).

Workbook 1 Workbook2
1725892767671.png
1725892803103.png
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Filter may work for you:
I would have to copy/paste for each different SO number and, if there's more items in workbook1 for an SO than line items for an SO in workbook two, it would give me a spill error. Hoping for some type of Index/Match formula, but I haven't been able to craft one.
 
Upvote 0
I would have to copy/paste for each different SO number and, if there's more items in workbook1 for an SO than line items for an SO in workbook two, it would give me a spill error. Hoping for some type of Index/Match formula, but I haven't been able to craft one.
Why don't you wrap it in the TEXTJOIN function? That way you can have multiple items appear in one cell. If you don't want duplicates wrap the filter function in the UNIQUE function.
 
Upvote 1
Why don't you wrap it in the TEXTJOIN function? That way you can have multiple items appear in one cell. If you don't want duplicates wrap the filter function in the UNIQUE function.
I am looking to do a line by line comparison so unfortunately that would not be very practical. dreid1011's solution of a filter function is working well, just a bit tedious as I have to copy that formula for each new SO. There's another option that appeared in the Similar Threads below where a solution was offered for a similar question, but it puts all copies in new columns on the same row; again, not ideal, but effective.

Outside of filter spills, I don't believe that functionality exists, at least with out some form of macros involved.

 
Upvote 0
You could use the filter function & wrap it in CHOOSEROWS, something like
Excel Formula:
=CHOOSEROWS(FILTER(Sheet1!$C$2:$C$100,Sheet1!$B$2:$B$100=B2),COUNTIFS(B$2:B2,B2))
 
Upvote 1
You could use the filter function & wrap it in CHOOSEROWS, something like
Excel Formula:
=CHOOSEROWS(FILTER(Sheet1!$C$2:$C$100,Sheet1!$B$2:$B$100=B2),COUNTIFS(B$2:B2,B2))
That did the trick! Now I need to include another column, how would it be modified? Now I need to bring pricing over to match both the SO number and the item number.

1725904953098.png
 
Upvote 0
How about
Excel Formula:
=CHOOSEROWS(FILTER(Sheet1!$C$2:$D$100,Sheet1!$B$2:$B$100=B2),COUNTIFS(B$2:B2,B2))
which will return both columns
 
Upvote 1
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,596
Messages
6,179,802
Members
452,943
Latest member
Newbie4296

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