MovingCellContents

[JohnGiacon]

Hi.
I have data in this form:

Play.xlsx
	F	G
5	10	cat
6		dog
7	11	hat
8	12	mat
9		rug
10		carpet
11	13	he
12		they
Sheet1

And I would like it to end up in this form:
[the maximum number of Senses is 5]

Play.xlsx
	B	C
4	ID	Senses
5	10	1. cat; 2. dog
6	11	1. hat
7	12	1. mat; 2. rug; 3. carpet
8	13	1. he; 2. they
Sheet1

Ignore the row and column info.
Thanks, John

 

[dermie_72]

Here is my suggestion.
Click on the Range in Column F, Select Home/Find & Select, Go To Special, Choose blanks. Press = then press the up arrow and press control + Enter.
You can use

=UNIQUE(F5:F13)

to get the list of unique codes in the range (Adjust the formula to suit your range.)
Then assuming your data starts in B5, in Cell C5 use this formula:

=TEXTJOIN("; ",TRUE,IF(($F$5:$F$13=B5),$G$5:$G$13, ""))

Adjust the ranges according to your criteria.

 

[Sam_D_Ben]

Another option, if range not an issue.

Book1
	A	B	C	D	E
1	10	cat		ID	Senses
2		dog		10	1. cat; 2. dog
3	11	hat		11	1. hat
4	12	mat		12	1. mat; 2. rug; 3. carpet; 4. mug; 5. paper
5		rug		13	1. he; 2. they
6		carpet
7		mug
8		paper
9		water
10	13	he
11		they
Sheet1

Sub test(): Dim s, r&, i&, o&, a$, b$, c&: Set s = Sheets("Sheet1")
s.Range("D1") = "ID": s.Range("E1") = "Senses"
r = s.Cells(s.Rows.count, 2).End(3).Row: o = 2
For i = 1 To r
If s.Cells(i, 1) <> "" Then
If a <> "" Then s.Cells(o, 4) = a: s.Cells(o, 5) = b: o = o + 1
a = s.Cells(i, 1): c = 1: b = "1. " & s.Cells(i, 2)
ElseIf s.Cells(i, 2) <> "" And c < 5 Then
c = c + 1
b = b & "; " & CStr(c) & ". " & s.Cells(i, 2)
End If
Next: If a <> "" Then s.Cells(o, 4) = a: s.Cells(o, 5) = b
End Sub

 

[JohnGiacon]

Here is my suggestion.
Click on the Range in Column F, Select Home/Find & Select, Go To Special, Choose blanks. Press = then press the up arrow and press control + Enter.
You can use

=UNIQUE(F5:F13)

to get the list of unique codes in the range (Adjust the formula to suit your range.)
Then assuming your data starts in B5, in Cell C5 use this formula:

=TEXTJOIN("; ",TRUE,IF(($F$5:$F$13=B5),$G$5:$G$13, ""))

Adjust the ranges according to your criteria.

Hi Dermie
Thanks for that; I will try it later - trying to get the job finished.
Best, John

Another option, if range not an issue.

Book1
	A	B	C	D	E
1	10	cat		ID	Senses
2		dog		10	1. cat; 2. dog
3	11	hat		11	1. hat
4	12	mat		12	1. mat; 2. rug; 3. carpet; 4. mug; 5. paper
5		rug		13	1. he; 2. they
6		carpet
7		mug
8		paper
9		water
10	13	he
11		they
Sheet1

Sub test(): Dim s, r&, i&, o&, a$, b$, c&: Set s = Sheets("Sheet1")
s.Range("D1") = "ID": s.Range("E1") = "Senses"
r = s.Cells(s.Rows.count, 2).End(3).Row: o = 2
For i = 1 To r
If s.Cells(i, 1) <> "" Then
If a <> "" Then s.Cells(o, 4) = a: s.Cells(o, 5) = b: o = o + 1
a = s.Cells(i, 1): c = 1: b = "1. " & s.Cells(i, 2)
ElseIf s.Cells(i, 2) <> "" And c < 5 Then
c = c + 1
b = b & "; " & CStr(c) & ". " & s.Cells(i, 2)
End If
Next: If a <> "" Then s.Cells(o, 4) = a: s.Cells(o, 5) = b
End Sub

Thanks Sam, worked beautifully. Time I gave up trying to write vba, since there is such great help available.
Cheers, John