Do I need macro for this?

[rahulbassi268]

Hello All,

Please help me with below problem where I have multiple rows of data for each ID and I wish to have only 1 row for each ID.

Student ID	Student Name	Academic Load	Topic	Relationship	Relative Name
100	James	F	Blah Blah 1	Mother	Cinthia
100	James	F	Blah Blah 1	Father	John
102	Robert	P	Blah Blah 2	Brother	Jack
102	Robert	P	Blah Blah 2	Father	Eric
102	Robert	P	Blah Blah 2	Sister	Jill
105	Jeremy	P	Blah Blah 3
106	Jake	P	Blah Blah 4	Father	Maxwell
107	Garry	F	Blah Blah 5		Ashley

Below is the output I wish to see

Student ID	Student Name	Academic Load	Topic	Mother	Father	Brother	Sister	Unknown
100	James	F	Blah Blah 1	Cinthia	John
102	Robert	P	Blah Blah 2		Eric	Jack	Jill
105	Jeremy	P	Blah Blah 3
106	Jake	P	Blah Blah 4		Maxwell
107	Garry	F	Blah Blah 5					Ashley

 

[Michael M]

Maybe this way...adjust your ranges to suit

 Sub MM1()
    Range("A2:F9").RemoveDuplicates (1)
End Sub

 

[kevin9999]

What version of Excel are you using?

 

[kevin9999]

@Peter_SSs advised you some time ago here to update your account details so helpers could see what version of Excel you are using - please do so.
I'm going to assume you have 365, and in answer to the question you posed in the post title, you could use this appraoch:
Sheet1:

Book1
	A	B	C	D	E	F
1	Student ID	Student Name	Academic Load	Topic	Relationship	Relative Name
2	100	James	F	Blah Blah 1	Mother	Cinthia
3	100	James	F	Blah Blah 1	Father	John
4	102	Robert	P	Blah Blah 2	Brother	Jack
5	102	Robert	P	Blah Blah 2	Father	Eric
6	102	Robert	P	Blah Blah 2	Sister	Jill
7	105	Jeremy	P	Blah Blah 3
8	106	Jake	P	Blah Blah 4	Father	Maxwell
9	107	Garry	F	Blah Blah 5		Ashley
Sheet1

Sheet2:

Book1
	A	B	C	D	E	F	G	H	I
1	Student ID	Student Name	Academic Load	Topic	Mother	Father	Brother	Sister	Unknown
2	100	James	F	Blah Blah 1	Cinthia	John
3	102	Robert	P	Blah Blah 2		Eric	Jack	Jill
4	105	Jeremy	P	Blah Blah 3
5	106	Jake	P	Blah Blah 4		Maxwell
6	107	Garry	F	Blah Blah 5					Ashley
Sheet2

Cell Formulas
Range	Formula
A2:D6	A2	=UNIQUE(Sheet1!A2:D9)
E2:H6	E2	=XLOOKUP(1,(Sheet1!$A:$A=$A2)*(Sheet1!$E:$E=E$1),Sheet1!$F:$F,"")
I2:I6	I2	=IF(ISBLANK(XLOOKUP(1,(Sheet1!$A:$A=$A2)*(Sheet1!$E:$E=""),Sheet1!$F:$F,"")),"",XLOOKUP(1,(Sheet1!$A:$A=$A2)*(Sheet1!$E:$E=""),Sheet1!$F:$F,""))
Dynamic array formulas.

 

[Peter_SSs]

Maybe this way...adjust your ranges to suit

 Sub MM1()
    Range("A2:F9").RemoveDuplicates (1)
End Sub

Did you look at the desired output?

 

[rahulbassi268]

@Peter_SSs advised you some time ago here to update your account details so helpers could see what version of Excel you are using - please do so.
I'm going to assume you have 365, and in answer to the question you posed in the post title, you could use this appraoch:
Sheet1:

Book1
	A	B	C	D	E	F
1	Student ID	Student Name	Academic Load	Topic	Relationship	Relative Name
2	100	James	F	Blah Blah 1	Mother	Cinthia
3	100	James	F	Blah Blah 1	Father	John
4	102	Robert	P	Blah Blah 2	Brother	Jack
5	102	Robert	P	Blah Blah 2	Father	Eric
6	102	Robert	P	Blah Blah 2	Sister	Jill
7	105	Jeremy	P	Blah Blah 3
8	106	Jake	P	Blah Blah 4	Father	Maxwell
9	107	Garry	F	Blah Blah 5		Ashley
Sheet1

Sheet2:

Book1
	A	B	C	D	E	F	G	H	I
1	Student ID	Student Name	Academic Load	Topic	Mother	Father	Brother	Sister	Unknown
2	100	James	F	Blah Blah 1	Cinthia	John
3	102	Robert	P	Blah Blah 2		Eric	Jack	Jill
4	105	Jeremy	P	Blah Blah 3
5	106	Jake	P	Blah Blah 4		Maxwell
6	107	Garry	F	Blah Blah 5					Ashley
Sheet2

Cell Formulas
Range	Formula
A2:D6	A2	=UNIQUE(Sheet1!A2:D9)
E2:H6	E2	=XLOOKUP(1,(Sheet1!$A:$A=$A2)*(Sheet1!$E:$E=E$1),Sheet1!$F:$F,"")
I2:I6	I2	=IF(ISBLANK(XLOOKUP(1,(Sheet1!$A:$A=$A2)*(Sheet1!$E:$E=""),Sheet1!$F:$F,"")),"",XLOOKUP(1,(Sheet1!$A:$A=$A2)*(Sheet1!$E:$E=""),Sheet1!$F:$F,""))
Dynamic array formulas.

Thanks Kevin
This worked

 

[kevin9999]

Good to hear, and thanks for the feedback

 

[Peter_SSs]

So can you please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using (like in my profile below) as the best solution often varies by version? (Don’t forget to scroll down & ‘Save’)