customer filter

[silverxx12]

I have data for customers and the amounts they have paid.

In the amounts column i have figures and word.

What i wish to do is exclude/ filter from the list customers which only say large in the amount column.

However the problem i have is some customer which also have figures which i do not wish to exclude.

So basically i what to look down the whole list of customers for j blogg ( example) and see in the corresponding amount column. if it says large on every
amount line then i want to exclude them from the list

Any ideas plz?

Regards

Silver

 

[Mike Blackman]

Hi,

Are you trying to use the autofilters for this? If so then try, CUSTOM - Does Not Equal - Large.

Or are you trying to remove these items?

 

[silverxx12]

have the auto filter on but the problem is some of those customers which say large also on other dates have a figure.

so all i want to exclude are the customers which say "large" in every amount line

eg

cust. amount
aa 100
aa large
bb large
bb large

so in this example i want to see all of aa but customer bb dont want to see him

 

[Mike Blackman]

Hi,

In that case try;

Sub HideRows()

Dim lRow As Long

lRow = Range("A" & Rows.count).End(xlUp).Row

For i = 2 To lRow
    If Application.SumIf(Columns("A"), Cells(i, 1), Columns("B")) = 0 Then Rows(i).Hidden = True
Next i

End Sub

 

[silverxx12]

my vba is not that good - its probably really simple - but just a bit confusing is there a work around this via excel formuals?

 

[Mike Blackman]

Hi,

Assuming I have grasped your data correctly yhis should only need to be pasted into the VBE. Right Click on the sheet Tab and hit view code, paste this in and from Excel, Tools Menu - Macro - Macro's and find this code.

Alternatively look into using Advanced Filter with a Criteria Range here.

 

[silverxx12]

Hi appreciate it

but could i ask one more favour if you could just explain step by step the vba so at least i can understand and learn from what you have written and thereby get more familaiar with vba

i copied the macro into the vbe but it is hiding all my data plz can you explain it so i can modify

much appreciated

silver

 

[Mike Blackman]

Hi,

Sub HideRows()

Dim lRow As Long

lRow = Range("A" & Rows.count).End(xlUp).Row 'Finding the Last Row of Data you have in Column A

For i = 2 To lRow 'Creating a Loop to Loop through each of you rows from 2 to the last row
    If Application.SumIf(Columns("A"), Cells(i, 1), Columns("B")) = 0 Then Rows(i).Hidden = True ' IF Statement - IF(SUMIF(A:A,A1,B:B)=0,Hide Column
Next i 'Changes the Value of i thru the loop

End Sub

 

[silverxx12]

hi still hiding everything

<TABLE style="WIDTH: 295pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=393 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 4864" width=137><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" width=64 height=18>Type</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Date</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 103pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=137>Source Name</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Memo</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Amount</TD></TR></TBODY></TABLE>

these are my headings i dont know if that is having any effect on the macro

 

[Mike Blackman]

Hi,

Try this;

Sub HideRows()

Dim lRow As Long

lRow = Range("[COLOR="Navy"]A[/COLOR]" & Rows.Count).End(xlUp).Row

Cells.EntireRow.Hidden = False

For i = 2 To lRow
    If Application.SumIf([COLOR="Navy"]Columns("A"), [/COLOR]Cells(i, [COLOR="Lime"]1[/COLOR]), [COLOR="Red"]Columns("E")[/COLOR]) = 0 Then Rows(i).Hidden = True
Next i

End Sub

Hi I have changed the Red part to Column E for the SUM, you need to change the NAVY parts to read the column that your customer is in and the Green Part to the Column Number that the customer falls into, ie. Column A = 1, Column E = 5