Excel 2010 Query

April 22, 2011 at 02:32:25
Specs: Windows 64
I'm fairly new to Excel. I have a spreadsheet of 250,000 rows which I have sorted by column D to find certain words. This gives me 648 rows of which each has a separate reference number in column A. However those reference numbers are also shared by other rows which do not meet the criteria from column D. How do I produce a worksheet which includes every row that matches the 648 reference numbers in column A whether it meets the sort criteria from column D or not? (That is where a shared reference number has one row which meets the sort criteria)


See More: Excel 2010 Query

Report •

April 22, 2011 at 06:20:51
Let me first see if I understand this correctly.

The data set below is sorted on Column D. If, for example, "Fred" is the word you are interested in, you are also looking to extract any other words associated with the numbers 1 and 3 (e.g. Bill, Harry & Tom).

Is that correct?

	A	B	C	  D
1	1			Bill
2	1			Fred
3	1			Fred
4	3			Fred
5	3			Fred
6	1			Harry
7	2			Sue
8	2			Sue
9	2			Barb
10	2			Sue
11	3			Tom
12	3			Tom

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 22, 2011 at 06:58:01
Yes that is correct but I don't want to include the rows with Harry & Tom in them if the reference in column A doesn't have a Fred linked to that same number. So

13 4 Tom - Do not include
14 5 Harry - Do not include
15 3 Tom - Include
16 1 Sue - Include
17 2 Tom - Do not include

So in your example I want every row that relates to a reference in column A that has a Fred in column D against at least one of those rows. Hope that's clear.


Report •

April 23, 2011 at 07:05:16
Give this a try.

It does not require you to sort the data on Sheet 1, although it won't matter if you do. It will ask for the "word" to find in Column D and then begin copying all data with the same Reference Numbers over to Sheet 2.

Sheet 2 will be grouped by Reference Numbers. It would be fairly simple to add code to sort Sheet 2 by the data in Column D if required.

Note: As written, it will clear the data from Sheet 2 so that you start with a clean sheet. It doesn't deal with column labels or anything like that. Any customization would require additional coding.

I suggest that you try this code in a backup copy of your workbook since macros can not be easily undone.

Sub RefSorter()
'Clear Sheet 2 to start with an empty sheet
'Determine how many rows are in Sheet1
  lastSht1_A_rw = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
'Get Search String From User
  myString = Application.InputBox("Enter Search String")
'Find the first occurence of the Search String and
'Copy all data with the same reference number to Sheet 2
    Set c = Worksheets(1).Columns(4).Find(myString, _
            LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        nxtAddress = c.Address
'Check if the Reference Number has already been copied to Sheet 2
'Don't copy any data with the same Reference Number
            refNum = Cells(c.Row, 1)
             With Sheets(2).Columns(1)
               Set r = .Find(refNum)
                If r Is Nothing Then
                 For chkRef1 = 1 To lastSht1_A_rw
                  If Cells(chkRef1, 1) = refNum Then
                   nxtrw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
                   Rows(chkRef1).EntireRow.Copy _
                     Destination:=Sheets(2).Cells(nxtrw, 1)
                  End If
                End If
             End With
'Find the next occurance of the Search String and loop
     Set c = Range(Cells(Range(nxtAddress).Row + 1, 4), _
                   Cells(lastSht1_A_rw, 4)).Find(myString)
      If Not c Is Nothing Then
       nxtAddress = c.Address
      End If
'Exit if search string not found
     On Error Resume Next
         Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

April 23, 2011 at 13:45:00
Column D is in reality a list of goods and when I enter a text string of TOBACCO which should produce a few thousand results it appears to work away for around 5 or 6 minutes before Excel stops working. From the autosaved version there doesn't appear to be anything copied to Sheet 2.

Report •

April 24, 2011 at 10:22:43
Is Tobacco in each cell by itself or is it part of a longer string, include any spaces?

Did you enter Tobacco in upper case or in the matching case of how it appears in Column D?

What do you mean by "Excel stops working"?

Try cutting the sheet down to a reasonable size but which still contains enough varying data so that you can see what it's doing.

Using the example data I posted earlier, the code works fine, since it was written with that example as the data set. Obviously there is something different with your data set.

Try changing this one line:

Current Line:

     Set c = Range(Cells(Range(nxtAddress).Row + 1, 4), _
                   Cells(lastSht1_A_rw, 4)).Find(myString)

New Line:

     Set c = Sheets(1).Range(Cells(Range(nxtAddress).Row + 1, 4), _
                   Cells(lastSht1_A_rw, 4)).Find(myString)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 24, 2011 at 12:28:24
I used TOBACCO as an example as it does appear alone although I would like to find all examples including where it is part of a longer string eg TOBACCO PIPE CLAY. I made sure the case was the same and renamed the Sheet to Sheet 1 to match. I got the error message "Excel has stopped working" and it recovers a version of the worksheet. I will try a smaller dataset as you suggest and post back. Thank you for your efforts - they are appreciated.

Report •

April 24, 2011 at 12:30:55
OK - I have limited the worksheet to the first 1,000 rows and your first macro works perfectly. I will look further into what is happening by expanding this bit by bit.

Report •

April 24, 2011 at 15:14:03
Try adding this line just below Sub RefSorter:

Dim lastSht1_A_rw As Long

I got lazy and didn't declare any variables. With 250K rows, the variable that determines how many rows are in Sheet1 may need to be set up to hold a larger number.

If you want to search for partial strings, change:

   Set c = Worksheets(1).Columns(4).Find(myString, _
            LookIn:=xlValues, lookat:=xlWhole)

to be:

   Set c = Worksheets(1).Columns(4).Find(myString, _
            LookIn:=xlValues, lookat:=xlPart)

Just an FYI...

There is no need to rename any sheets since my code does not refer to any sheets by name.

Sheets(1), Sheet(2), etc. refer to the sheets in the order in which they are in the workbook, regardless of what the Sheet tab name is.

Sheets("Sheet1") and Sheets("Results") would refer to sheets named Sheet1 and Results since the quotes denote a text string.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 25, 2011 at 11:47:52
I have now used a subset of data of around 78,000 rows. I have used a text string which doesn't produce many results and the macro has worked fine. (Done this twice with two different strings.) I have managed to get the string "TOBACCO" to work although it failed many times. I tried watching from Sheet 2 and the rows weren't copied as it went along but every time (except one) Excel fell over at some point or another before the end. It seems like there was just too much data to copy.

I am happy to work in chunks of this size and do thank you for the time you have put into helping me with this.


Report •

Ask Question