Macro for Advanced Filter

Hey guys,

I've been working on a spreadsheet and pretty much have it finished up except for this one extremely frustrating detail.

The point of the sheet is to help caluculate material requirements. Column A in the example consists of text values that are being pulled from several cells on another sheet using & (for example, A2&" "&B2&" "&C2), and Column B is being pulled from the quantity of that material required for that line on the other sheet. There will be repeat values in column A.

I need to pull the unique values out of A, and sum the required material for each one. The Advanced filter was giving me some issues when I was trying to filter the pulled in values (it was filtering the ""A2&" "&B2&" "&C2"" instead of the actual text strings they were pulling), hence why the macro pastes the values in columns C and D and does the filter on them instead.

While this method actually works, I've noticed that it always pulls the first two lines out despite the fact that they may be unique. I've double checked everything to make sure that the values in Column A are being pulled in the same format, same spacing and all, yet it still pulls both the first and second row as unique values. Can anybody help me out and explain what might be going on here? Thanks.

Also, if this belongs in the Excel functions section let me know, I wasn't sure which category it fell into more.

snb,

thanks for the quick feedback, but the cells in question aren't merged. And unmerging their headings doesn't change the output. Any other thoughts?

Nevermind, I figured it out. Thanks though!

The top cell in the range is considered to be the column heading (field name). The UNIQUE only applies to the cells below the heading. The modified code below appears to work.


Range("E2").ClearContents
Range("C2:C73").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"E2"), Unique:=True
Range("F3").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R3C3:R73C3,RC[-1],R3C4:R73C4)"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F18"), Type:=xlFillDefault
Range("E2") = "UNIQUE " & Range("E2")