Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?

Question

I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report to retrieve this information, I get a list of all the Parent Items the material is used in but they appear in different cells. I want to retrieve all of these Parent Items and add them into a single cell within a separate excel spreadsheet. Is there a formula I can use to do this? Below is an example of what I am working on.

I am looking to retrieve the multiple Parent items listed in column C of screenshot #2 that a part number is used in and add them to the cells in the "Where Used" column as shown in Screenshot #1.

Thank you in advance for any help you can provide!

Answer 1

You have not provided enough detail to allow a detailed answer; but generally VLOOKUP() is likely one of the alternative function(s) to use.

References between spreadsheets can be created by
a) having both open,
b) select a cell to contain a formula, type a single =,
c) switch to the other spreadsheet and click a cell to "pick a value from",
d) the first spreadsheet (cell) should now contain a reference to that cell.
e) cut and paste where you wish to use it.

NOTE: the second / referenced spreadsheet has to be open when you use the first one.

---

Counting the matching cells should be possible.
Finding the first one (VLOOKUP, or MATCH) is possible.

Getting a LIST of the matching ones into a single cell - never tried that, not seen it as a doable thing, and I cannot see that as "versatile".

Array formulas or functions might have been another possible way to handle it.
But these do not return a list of values into a single cell(?).

I'd say you need to create a database and then issue searches to summarize the content from there (i.e. SQL; select ... where ...)

Pivot-tables might have options that could be useful, but "single cell" lists are not there either.

Beyond that; Python with Pandas (programmable access to Excel sheets, Pivot tables and all that). Pandas has options that may be used in a manner similar to as if the Excel sheet was a database.