How to get information from several cells into one cell

[RedAppl3]

Hi guys,

Made an account here because i have been trying to figuring this out for hours now to no prevail. Maybe you guys can find a solution.
I have a table with several thousand article numbers, and i want to create a new table where all the sizes for the same article number are gathered up.

Is this even possible?


My table:

Article number

Size

<tbody>
</tbody>

011.0400 <tbody> </tbody>	8
011.0755 <tbody> </tbody>	9
011.245 <tbody> </tbody>	L
011.245 <tbody> </tbody>	M
011.624 <tbody> </tbody>	9
012.422U <tbody> </tbody>	10
012.422U <tbody> </tbody>	11
012.422U	9

<tbody>
</tbody>

Wanted Outcome:

Article Number	All Sizes
011.0400 <tbody> </tbody>	8
011.0755 <tbody> </tbody>	9
011.245 <tbody> </tbody>	L, M <tbody> </tbody>
011.624 <tbody> </tbody>	9 <tbody> </tbody>
012.422U <tbody> </tbody>	10, 11, 9 <tbody> </tbody>

<tbody>
</tbody>

 

[wideboydixon]

Hi and welcome to the forum. Some questions:

1. Which version of Excel do you have and is it Windows or Mac?
2. Do you want this only using formulas or is VBA acceptable?
3. On the new table, do you already have the list of unique article numbers or do you want that generating also?

WBD

 

[lrobbo314]

Good questions by wideboydixon. The easiest way is just to make a pivot table out of your data. Then you can just add Article Number and Size fields to the pivot table Rows.

I made a custom function if that interests you. I took your Article numbers, copied them to a new column, and then removed duplicates. Then, in the next column, I have the custom function which has 2 arguments. 1st argument is the cell that you are looking at, i.e. the first cell in the column with the duplicates removed. 2nd argument, your entire table with article numbers and sizes. Here's the code.

Function Combine(s As String, r As Range) As String
Dim cel As Range
Dim AR()
Dim res As String


AR = r.Value


For i = 1 To UBound(AR)
    If AR(i, 1) = s Then
        res = res & AR(i, 2) & ", "
    End If
Next i


res = Left(res, Len(res) - 2)
Combine = res
End Function

And your formula would look like this...

=Combine(F2,$A$2:$B$9)

Like wideboydixon is alluding to, you could automate some of the processes going on here with VBA and basically do everything in one fell swoop.

 

[RedAppl3]

Hi and welcome to the forum. Some questions:

1. Which version of Excel do you have and is it Windows or Mac?
2. Do you want this only using formulas or is VBA acceptable?
3. On the new table, do you already have the list of unique article numbers or do you want that generating also?

WBD

Hi,

1. Im using the 2016 version on Windows

2. Formulas would be prefered as i don't really know what VBA is. I am only a few months in to learning excel, but i will read up on this.

3. I already have the list of unique article numbers

Thanks

 

[jmacleary]

Hi there

This is a bit of a clunky solution but I hope it gets you on your way. I am assuming your table starts at A1.

Put this formula in C2 and copy down: =IF(ISNA(VLOOKUP(A2,A3:A$9999,1,FALSE)),B2,VLOOKUP(A2,A3:C$9999,3,FALSE)&","&B2) (9999 is any row number after the last row of data)

Set up a list of unique article numbers in column F with the first value in row 2 - one way is to copy all column A values into column F (or anywhere really) and use the remove duplicates function.

Put the following formula in G2: =VLOOKUP(F2,A$2:C$9999,3,FALSE) and copy down.

Obviously, if the list of articles changes, you will need to rebuild the list of unique values.

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Im using the 2016 version on Windows

I'm assuming that is the 2016 version through Office 365 as that has the TEXTJOIN function I have used below.

This is an array formula so should be entered without the {} but, after adjusting the ranges to suit your larger data, confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F
1	Article number	Size			Article number	Sizes
2	11.04	8			11.04	8
3	11.0755	9			11.0755	9
4	11.245	L			11.245	L, M
5	11.245	M			11.624	9
6	11.624	9			012.422U	10, 11, 9
7	012.422U	10
8	012.422U	11
9	012.422U	9
10
Combine Values 1

 

[wideboydixon]

Ah. Beaten to it when I had a break

I was angling for TEXTJOIN too.

WBD

 

[RedAppl3]

Hi there

This is a bit of a clunky solution but I hope it gets you on your way. I am assuming your table starts at A1.

Put this formula in C2 and copy down: =IF(ISNA(VLOOKUP(A2,A3:A$9999,1,FALSE)),B2,VLOOKUP(A2,A3:C$9999,3,FALSE)&","&B2) (9999 is any row number after the last row of data)

Set up a list of unique article numbers in column F with the first value in row 2 - one way is to copy all column A values into column F (or anywhere really) and use the remove duplicates function.

Put the following formula in G2: =VLOOKUP(F2,A$2:C$9999,3,FALSE) and copy down.

Obviously, if the list of articles changes, you will need to rebuild the list of unique values.

This is amazing! Worked like a charm.


Thank you everyone for the help! I will be looking more at all the respones and different solutions when i come home from work just so i can learn!