customer filter

silverxx12

Board Regular
Joined
Jan 23, 2009
Messages
99
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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

Board Regular
Joined
Jan 23, 2009
Messages
99
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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi,

In that case try;

Code:
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

Board Regular
Joined
Jan 23, 2009
Messages
99

ADVERTISEMENT

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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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

Board Regular
Joined
Jan 23, 2009
Messages
99

ADVERTISEMENT

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
 
Last edited:

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi,

Code:
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

Board Regular
Joined
Jan 23, 2009
Messages
99
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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi,

Try this;

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,857
Messages
5,598,484
Members
414,242
Latest member
Lehel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top