Macro to compare two columns and keep row with matching data

[VirtualInsanity]

Hi team, I've been trying to find a macro code to compare two columns (column A with column C) and keep any rows of data that find a match i.e. if A3 matches C6 then the the entire 6th row will be kept (B6:AK6). Any help is appreciated!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Using XL2BB could you give us a small set of dummy sample data "before" and "after"?

if A3 matches C6 then the the entire 6th row will be kept (B6:AK6)

.. and if A3 doesn't match with anything in column C what happens? This may well be answered by the sample requested above but please make sure it is clear what to do when there are matches and when there are not matches.

 

[VirtualInsanity]

Hi Peter, thanks for this. I've updated my profile. I'm using O365 on Windows, but I'm afraid I'm not allowed to install programmes on my work computer. I've uploaded an image of some dummy data though and an image of the desired result to give you an idea. Hopefully that's enough? If not I'll have to wait until I'm home and do it on my personal computer. I either want to delete the rows that don't come up with a match or simply create a new list with the matched data on a new spreadsheet within the workbook. I don't really mind which. I just need the rows that match the names in column A.

 

[Peter_SSs]

Unfortunately we can't copy from an image to test. But do you need a macro? Could you just use a formula like this?

VirtualInsanity.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Attendee	ID	Full Name	Hdr4	Hdr5	Hdr6	Hdr7			ID	Full Name	Hdr4	Hdr5	Hdr6	Hdr7
2	a	1	d	79	39	39	53			3	c	27	58	44	16
3	b	2	e	77	13	68	18			5	b	44	8	4	16
4	c	3	c	27	58	44	16			6	a	56	23	97	42
5		4	s	37	24	25	99
6		5	b	44	8	4	16
7		6	a	56	23	97	42
8		7	g	4	80	19	73
9
10
Sheet1

Cell Formulas
Range	Formula
J2:O4	J2	=IFERROR(DROP(FILTER(Table1,ISNUMBER(MATCH(Table1[Full Name],Table1[Attendee],0))),,1),"")
Dynamic array formulas.

 

[VirtualInsanity]

Oh! No apparently I don't. That's perfect. Thanks so much for your help

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your profile.