Need multiple cell value in single cell based on unique Material ID

What is the MAXIMUM number of POs in a single cell? Is a VBA solution OK with you?

Take a look (enable macros). Happy to explain if it meets your needs.

It wont work on another sheet unless you set it up correctly. That is why I asked you if it was OK and then I would explain it.
You did not answer my Q about the maximum number of POs...

Formula is impossible. Is VBA OK? If so, I will explain in an hour or so. Am out for a while.

Please try with helper column

Data, E2 copy down

=IFERROR(D2&CHAR(10)&INDEX(E3:E33,MATCH(B2,B3:B33,)),D2)

Required Data Format B2 copy down

=INDEX(Data!$E$2:$E$33,MATCH(A2,Data!$B$2:$B$33,))



Or for Excel365 with Textjoin, no need helper column E in sheet Data
Required Data Format D2 press Ctrl+Shift+Enter copy down

=Textjoin(CHAR(10),,IF(Data!$B$2:$B$33=A2,Data!$D$2:$D$33,""))

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Then use this array formula:

=concatall(IF(Data!$B$2:$B$33='Required Data Format'!A2,Data!$A$2:$A$33&" "&Data!$C$2:$C$33,""),CHAR(10))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...