VBA to compare filtered list to another list

[ChristineJ]

Column B has a list of names and column C has a corresponding amount for each name. The length of this list could vary.

Cell F3 contains one of the names, and below that a list of numbers. In this case there are four numbers typed in, but there could be more or fewer.

I'd like to use a VBA macro to return either "Yes" or "No" in cell F1. "Yes" should be returned if the number of amounts, and the values of those amounts, for Tom in column C match the amounts for Tom in column F, in the same order.

In other words, if column B were filtered for Tom, the list of amounts for Tom would be exactly the same as the list beginning in cell F4.

Is there a way to do this with VBA? Thanks!

 

[Fluff]

You can do that with a simple formula
=IF(SUMIFS(C:C,B:B,F3)=SUM(F4:F100),"Yes","No")

 

[ChristineJ]

Thanks for helping me out again!

I don't just want to know if the sums equal, which of course they would if both ranges of numbers were exactly the same.

I mainly want to know if the two lists of numbers, column C with only Tom and column F, are exactly the same in the same order.

So 1, 2, 3, 4 in column C and 1, 2, 4, 3 in column F would return "No". Likewise, 1, 2, 3, 4 in column C and 1, 2, 2, 5 would return a "No".

Thanks, CJ

 

[Fluff]

Ok, what version of Excel are you using?
Please update your account details to show this, as it affects what functions you have available.
Thanks

 

[ChristineJ]

Here is a screen shot of the version. Thanks! CJ

 

[Fluff]

Ok, thanks for that, please update your account details to show that you are using 365.
I'm currently having to repair Office as Outlook blew up, but will post a solution shortly.

 

[Fluff]

Ok, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1					Yes	No	No
2
3		Joe	18		Tom	Tom	Tom
4		Mac	14		20	20	20
5		Tom	20		12	16	12
6		Joe	9		16	12	16
7		Mac	18		20	20	21
8		Joe	14
9		Joe	18
10		Tom	12
11		Joe	13
12		Joe	11
13		Tom	16
14		Ned	17
15		Ned	17
16		Ned	10
17		Tom	20
18		Joe	20
Data

Cell Formulas
Range	Formula
E1:G1	E1	=IF(TEXTJOIN(",",1,IF($B$3:$B$20=E3,$C$3:$C$20,""))=TEXTJOIN(",",1,E$4:E$10),"Yes","No")

 

[ChristineJ]

This will do exactly what I need (THANKS!), but I am having trouble getting it to work.

If I enter your formula in E1, or in any cell in row 1 or 2, it returns #VALUE!. If I enter it anywhere in row 3 or further down, it returns "No" when it should be "Yes." E1 is selected in the screen shot below.

I tried breaking down your formula into parts, in red and blue in my screen shot. It does not seem that on my sheet it is pulling only Tom's amounts from column C. All cells are formatted as "General".

I'm not sure how else to troubleshoot. CJ

 

[Fluff]

Oops, I keep forget to mention this.
In your version of Xl you will need to confirm the formula with Ctrl Shift Enter & not just Enter.

 

[ChristineJ]

Works perfectly! Thanks you so much for all the help. This formula will really streamline the project I am working on!