Put multiple cell into one cell if name match

**Swaski** · 09-22-2016, 03:43 PM

Hello Excel Experts,

Need help with the following.

I have a bunch of names with different box numbers. I need to gather all the box number into one cell matching the name. Any help is greatly appreciated.

Example

Original List
Name Box No.
Pete 1
David 2
Jannet 4
David 2
Al 1
Lucy 66
Al 2
Al 3
Lucy 99

Final List
Pete 1
David 2 2
Jannet 4
Al 1 2 3
Lucy 66 99

**Tony Valko** · 09-22-2016, 04:21 PM

Is this what you want...

Data Range

	A	B	C	D	E
1	Name	Box No.	------	Name	Box No.
2	Pete	1		Pete	1
3	David	2		David	2 2
4	Jannet	4		Jannet	4
5	David	2		Al	1 2 3
6	Al	1		Lucy	66 99
7	Lucy	66
8	Al	2
9	Al	3
10	Lucy	99

**Swaski** · 09-22-2016, 04:23 PM

Hi Tony,

Yes. How do I do that with formula??

**Tony Valko** · 09-22-2016, 04:28 PM

You would need a VBA function to do that.

Are you OK with that?

**Swaski** · 09-22-2016, 04:31 PM

If it can make it work. I would probly need to adjust the Rows and Columns according to my actual data.

**Tony Valko** · 09-22-2016, 04:36 PM

Originally Posted by Swaski

I would probly need to adjust the Rows and Columns according to my actual data.

What does that mean?

Does that mean your data is not organized like the example table I posted?

**Swaski** · 09-22-2016, 04:43 PM

Im sorry for the confusion. I can make my list exactly like what you've listed.

**Tony Valko** · 09-22-2016, 06:42 PM

Have you ever used/worked with VBA code before?

**Swaski** · 09-22-2016, 06:50 PM

Slightly. I know the basic.

**Tony Valko** · 09-22-2016, 07:17 PM

Try this...

With your file open...

Copy the VBA code at the link below and paste it into a general module.

https://www.excelforum.com/showthread.php?p=3096647

Then, assuming this is your data...

Data Range

	A	B	C	D	E
1	Name	Box No.	------	Name	Box No.
2	Pete	1		Pete	1
3	David	2		David	2 2
4	Jannet	4		Jannet	4
5	David	2		Al	1 2 3
6	Al	1		Lucy	66 99
7	Lucy	66
8	Al	2
9	Al	3
10	Lucy	99

This array formula** entered in E2 and copied down:

=concatall(IF(A$2:A$10=D2,B$2:B$10,"")," ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You'll have to save the file as a macro enabled file in the *.xlsm format.

**Swaski** · 09-22-2016, 07:50 PM

Thank you so much.

I may have to look into what the macro does, but when I drag down, there's a value error. I see in the formula B$2:Bxxxxx, x are increasing as it goes down. But if I manually edit it so the array are the same, CTRL+SHFT+ENTER resolves it but I have about 1000 entries.

I'm assuming that what's the macro is for?

Put multiple cell into one cell if name match

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Re: Put multiple cell into one cell if name match

Thread Information

Users Browsing this Thread

Similar Threads

Insert multiple Images to match with filename in Merge cell and Unmerge cell

Check cell range for closest match of cell value (multiple words)

[SOLVED] multiple cell value to find match in range

Returning Multiple Values via Index Match that are < cell and = to another cell

Move value of a cell into another cell if multiple criteria is match in multiple ranges

Hide multiple rows below target cell based on a cell value match elsewhere.

[SOLVED] Data validation: cell must match another cell based on multiple cells

Bookmarks

Bookmarks

Posting Permissions