Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Filter
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 27
Default Filter
Hi,

I have huge data.
Column A has Customer ID. Data here is not repeated untill next change in
Customer ID.
Column B have Invoice Nos. There are many rows for 1 customer ID.
Column C have Invoice Date. There are many rows for 1 customer ID.
Column D....E....F....& so on.

I want data for any one selected Customer ID along with all the invoice
numbers, invoice dates & so on.

Using filter in Coulmn A we don't get the data as I require as the
immediately next few rows for the Customer ID in column A will be blank.

One solution is to copy the customer ID from column A & paste untill the new
one is spotted, but this is very time consuming exercise. I have data running
into tens of thousand of lines.

Please suggest how can I get the required result.

Cheers,
Mandeep
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,646
Default Filter
Hi Mandeep,

What about these subs:

Sub SingleCust()
firstrow = ActiveCell.Row
lastrow = Columns("A").Find(What:="*", _
After:=ActiveCell, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row - 1
If lastrow < firstrow Then
lastrow = Columns("B").Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
If firstrow 2 Then
Range("A2:A" & firstrow - 1).EntireRow.Hidden = True
End If
Range("A" & lastrow + 1 & ":A65536").EntireRow.Hidden = True
End Sub

Sub AllCust()
Cells.EntireRow.Hidden = False
End Sub

Usage:

Select cell (in Column A) with Cust.ID you want to see and run Singlecust
(you may assign a hot key to it).

Run AllCust to make all rows visible again!

Regards,
Stefi

€˛Mandeep Dhami€¯ ezt Ć*rta:

Hi,

I have huge data.
Column A has Customer ID. Data here is not repeated untill next change in
Customer ID.
Column B have Invoice Nos. There are many rows for 1 customer ID.
Column C have Invoice Date. There are many rows for 1 customer ID.
Column D....E....F....& so on.

I want data for any one selected Customer ID along with all the invoice
numbers, invoice dates & so on.

Using filter in Coulmn A we don't get the data as I require as the
immediately next few rows for the Customer ID in column A will be blank.

One solution is to copy the customer ID from column A & paste untill the new
one is spotted, but this is very time consuming exercise. I have data running
into tens of thousand of lines.

Please suggest how can I get the required result.

Cheers,
Mandeep
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,646
Default Filter
Another idea:
Sub SingleCust()
custid = InputBox("Enter a Customer ID!")
On Error GoTo NoCust
Columns("A").Find(What:=custid, _
After:=Range("A1"), _
LookAt:=xlWhole, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
firstrow = ActiveCell.Row
' If firstrow 0 Then
lastrow = Columns("A").Find(What:="*", _
After:=ActiveCell, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row - 1
If lastrow < firstrow Then
lastrow = Columns("B").Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
If firstrow 2 Then
Range("A2:A" & firstrow - 1).EntireRow.Hidden = True
End If
Range("A" & lastrow + 1 & ":A65536").EntireRow.Hidden = True
Exit Sub
NoCust:
MsgBox custid & " does not exist!"
End Sub

Regards,
Stefi


€˛Stefi€¯ ezt Ć*rta:

Hi Mandeep,

What about these subs:

Sub SingleCust()
firstrow = ActiveCell.Row
lastrow = Columns("A").Find(What:="*", _
After:=ActiveCell, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row - 1
If lastrow < firstrow Then
lastrow = Columns("B").Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
If firstrow 2 Then
Range("A2:A" & firstrow - 1).EntireRow.Hidden = True
End If
Range("A" & lastrow + 1 & ":A65536").EntireRow.Hidden = True
End Sub

Sub AllCust()
Cells.EntireRow.Hidden = False
End Sub

Usage:

Select cell (in Column A) with Cust.ID you want to see and run Singlecust
(you may assign a hot key to it).

Run AllCust to make all rows visible again!

Regards,
Stefi

€˛Mandeep Dhami€¯ ezt Ć*rta:

Hi,

I have huge data.
Column A has Customer ID. Data here is not repeated untill next change in
Customer ID.
Column B have Invoice Nos. There are many rows for 1 customer ID.
Column C have Invoice Date. There are many rows for 1 customer ID.
Column D....E....F....& so on.

I want data for any one selected Customer ID along with all the invoice
numbers, invoice dates & so on.

Using filter in Coulmn A we don't get the data as I require as the
immediately next few rows for the Customer ID in column A will be blank.

One solution is to copy the customer ID from column A & paste untill the new
one is spotted, but this is very time consuming exercise. I have data running
into tens of thousand of lines.

Please suggest how can I get the required result.

Cheers,
Mandeep
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,886
Default Filter
Hi Mandeep

You could insert a new column A and enter in cell A2
=IF(B2="",A1,B2)
Copy down column A as far as required, then use column A with
Autofilter.

--
Regards

Roger Govier


"Mandeep Dhami" wrote in
message ...
Hi,

I have huge data.
Column A has Customer ID. Data here is not repeated untill next change
in
Customer ID.
Column B have Invoice Nos. There are many rows for 1 customer ID.
Column C have Invoice Date. There are many rows for 1 customer ID.
Column D....E....F....& so on.

I want data for any one selected Customer ID along with all the
invoice
numbers, invoice dates & so on.

Using filter in Coulmn A we don't get the data as I require as the
immediately next few rows for the Customer ID in column A will be
blank.

One solution is to copy the customer ID from column A & paste untill
the new
one is spotted, but this is very time consuming exercise. I have data
running
into tens of thousand of lines.

Please suggest how can I get the required result.

Cheers,
Mandeep


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 27
Default Filter
Thanks a lot Stefi.

Both the macros are working wonderfully and I am getting the results as I
expected.
Thanks once again....

Cheers,
Mandeep

"Stefi" wrote:

Another idea:
Sub SingleCust()
custid = InputBox("Enter a Customer ID!")
On Error GoTo NoCust
Columns("A").Find(What:=custid, _
After:=Range("A1"), _
LookAt:=xlWhole, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
firstrow = ActiveCell.Row
' If firstrow 0 Then
lastrow = Columns("A").Find(What:="*", _
After:=ActiveCell, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row - 1
If lastrow < firstrow Then
lastrow = Columns("B").Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
If firstrow 2 Then
Range("A2:A" & firstrow - 1).EntireRow.Hidden = True
End If
Range("A" & lastrow + 1 & ":A65536").EntireRow.Hidden = True
Exit Sub
NoCust:
MsgBox custid & " does not exist!"
End Sub

Regards,
Stefi


€˛Stefi€¯ ezt Ć*rta:

Hi Mandeep,

What about these subs:

Sub SingleCust()
firstrow = ActiveCell.Row
lastrow = Columns("A").Find(What:="*", _
After:=ActiveCell, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row - 1
If lastrow < firstrow Then
lastrow = Columns("B").Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
If firstrow 2 Then
Range("A2:A" & firstrow - 1).EntireRow.Hidden = True
End If
Range("A" & lastrow + 1 & ":A65536").EntireRow.Hidden = True
End Sub

Sub AllCust()
Cells.EntireRow.Hidden = False
End Sub

Usage:

Select cell (in Column A) with Cust.ID you want to see and run Singlecust
(you may assign a hot key to it).

Run AllCust to make all rows visible again!

Regards,
Stefi

€˛Mandeep Dhami€¯ ezt Ć*rta:

Hi,

I have huge data.
Column A has Customer ID. Data here is not repeated untill next change in
Customer ID.
Column B have Invoice Nos. There are many rows for 1 customer ID.
Column C have Invoice Date. There are many rows for 1 customer ID.
Column D....E....F....& so on.

I want data for any one selected Customer ID along with all the invoice
numbers, invoice dates & so on.

Using filter in Coulmn A we don't get the data as I require as the
immediately next few rows for the Customer ID in column A will be blank.

One solution is to copy the customer ID from column A & paste untill the new
one is spotted, but this is very time consuming exercise. I have data running
into tens of thousand of lines.

Please suggest how can I get the required result.

Cheers,
Mandeep
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 27
Default Filter
Thanks Roger.

The formula mentioned by you works and I am able to use Filter in column A
and get the required data.

Cheers,
Mandeep


"Roger Govier" wrote:

Hi Mandeep

You could insert a new column A and enter in cell A2
=IF(B2="",A1,B2)
Copy down column A as far as required, then use column A with
Autofilter.

--
Regards

Roger Govier


"Mandeep Dhami" wrote in
message ...
Hi,

I have huge data.
Column A has Customer ID. Data here is not repeated untill next change
in
Customer ID.
Column B have Invoice Nos. There are many rows for 1 customer ID.
Column C have Invoice Date. There are many rows for 1 customer ID.
Column D....E....F....& so on.

I want data for any one selected Customer ID along with all the
invoice
numbers, invoice dates & so on.

Using filter in Coulmn A we don't get the data as I require as the
immediately next few rows for the Customer ID in column A will be
blank.

One solution is to copy the customer ID from column A & paste untill
the new
one is spotted, but this is very time consuming exercise. I have data
running
into tens of thousand of lines.

Please suggest how can I get the required result.

Cheers,
Mandeep



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,646
Default Filter
You are welcome! Thanks for the feedback!
Stefi


€˛Mandeep Dhami€¯ ezt Ć*rta:

Thanks a lot Stefi.

Both the macros are working wonderfully and I am getting the results as I
expected.
Thanks once again....

Cheers,
Mandeep

"Stefi" wrote:

Another idea:
Sub SingleCust()
custid = InputBox("Enter a Customer ID!")
On Error GoTo NoCust
Columns("A").Find(What:=custid, _
After:=Range("A1"), _
LookAt:=xlWhole, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
firstrow = ActiveCell.Row
' If firstrow 0 Then
lastrow = Columns("A").Find(What:="*", _
After:=ActiveCell, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row - 1
If lastrow < firstrow Then
lastrow = Columns("B").Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
If firstrow 2 Then
Range("A2:A" & firstrow - 1).EntireRow.Hidden = True
End If
Range("A" & lastrow + 1 & ":A65536").EntireRow.Hidden = True
Exit Sub
NoCust:
MsgBox custid & " does not exist!"
End Sub

Regards,
Stefi


€˛Stefi€¯ ezt Ć*rta:

Hi Mandeep,

What about these subs:

Sub SingleCust()
firstrow = ActiveCell.Row
lastrow = Columns("A").Find(What:="*", _
After:=ActiveCell, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row - 1
If lastrow < firstrow Then
lastrow = Columns("B").Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
If firstrow 2 Then
Range("A2:A" & firstrow - 1).EntireRow.Hidden = True
End If
Range("A" & lastrow + 1 & ":A65536").EntireRow.Hidden = True
End Sub

Sub AllCust()
Cells.EntireRow.Hidden = False
End Sub

Usage:

Select cell (in Column A) with Cust.ID you want to see and run Singlecust
(you may assign a hot key to it).

Run AllCust to make all rows visible again!

Regards,
Stefi

€˛Mandeep Dhami€¯ ezt Ć*rta:

Hi,

I have huge data.
Column A has Customer ID. Data here is not repeated untill next change in
Customer ID.
Column B have Invoice Nos. There are many rows for 1 customer ID.
Column C have Invoice Date. There are many rows for 1 customer ID.
Column D....E....F....& so on.

I want data for any one selected Customer ID along with all the invoice
numbers, invoice dates & so on.

Using filter in Coulmn A we don't get the data as I require as the
immediately next few rows for the Customer ID in column A will be blank.

One solution is to copy the customer ID from column A & paste untill the new
one is spotted, but this is very time consuming exercise. I have data running
into tens of thousand of lines.

Please suggest how can I get the required result.

Cheers,
Mandeep
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there a way to link Auto Filter values to a Cell SteveC Excel Worksheet Functions 2 May 1st 06 07:47 PM
Auto Filter - Protected sheet/workbook ronwill Excel Discussion (Misc queries) 3 January 10th 06 03:28 PM
Macro for Filter Switches ... Maybe??? Ken Excel Discussion (Misc queries) 4 February 25th 05 05:30 PM
Filter Switches vs Macro? Ken Excel Discussion (Misc queries) 3 February 24th 05 10:31 PM
advanced filter won't allow me to filter on bracketed text (-456.2 LucianoG Excel Discussion (Misc queries) 1 December 6th 04 08:38 PM


All times are GMT +1. The time now is 07:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement