Find result in a worksheet and use to copy information in another

[DThib]

Hello all,

The resource feeding one of my workbooks has changed.

The source now puts an entry everytime it is entered in the database. This results in many lines with almost identical information. I nee to adjust the logic at the start to account for this.
Here is the code:

Sub Workie()


  Dim LastRow, SecondRow As Long
  Dim i As Long
  Dim j As Long
    
    LastRow = Sheets("Initial Query Pull").Cells(Rows.Count, "A").End(xlUp).Row
    SecondRow = Sheets(3).Cells(Rows.Count, "B").End(xlUp).Row
    i = 1 + LastRow
    j = 1 + SecondRow
    For i = 1 To LastRow 'Each i In Sheets("Initial Query Pull")
        If Sheets("Initial Query Pull").Cells(i, 2) = "Work" And Sheets("Initial Query Pull").Cells(i, 27) _
        = "CLS" Is Nothing And Sheets("Initial Query Pull").Cells(i, 20) <> "" And (Sheets("Initial Query Pull").Cells(i, 11) _
        = " " Or Sheets("Initial Query Pull").Cells(i, 11) = "") Then
             Sheets("Workable").Cells(j, 1) = Format(Now(), "DD-MMM-YYYY")
             'Complaint ID
             Sheets(3).Cells(j, 2) = Sheets("Initial Query Pull").Cells(i, 1).Value


             j = j + 1
        End If
    Next i


End Sub

The code needs to account for column 2 for status and then column 3 for name. If these two are found look to column 1 for SampleID and then find the last row (potentially 1,000) that does not state "CLS" in Column 27 and get information.

Any ideas?

 

[NoSparks]

Re: Extremely complex puzzle to find result in a worksheet and use to copy information in another

Your post is unclear as to what you're trying to accomplish.

This is what the posted code does...
Steps down each row of sheet "Initial Query Pull" (by incrementing i) looking for cells of that row to satisfy the If statement,
if not all satisfied, moves on to the next row.
When the If statement is satisfied,
1.) puts the current date into column "A" of the next row (determined by j) on sheet "Workable"
2.) puts Sheets("Initial Query Pull").Cells(i, 1).Value into column "B" , on whatever row j is, on whatever sheet is third in your tab line-up.

I suspect both 1 & 2 should both be sheet "Workable", but as written that may not be the case.

Your last paragraph needs some things clarified...
The code needs to account for column 2 for status and then column 3 for name.
Column 2 and 3 of what sheet ?
What does 'account for' mean and how would one know if these are accounted for ?

If these two are found look to column 1 for SampleID
Found where and what's the significance of SampleID ?

and then find the last row (potentially 1,000) that does not state "CLS" in Column 27
Find the last row of what ?

and get information.
What information and do what with it ?

 

[DThib]

Re: Extremely complex puzzle to find result in a worksheet and use to copy information in another

Your post is unclear as to what you're trying to accomplish.

This is what the posted code does...
Steps down each row of sheet "Initial Query Pull" (by incrementing i) looking for cells of that row to satisfy the If statement,
if not all satisfied, moves on to the next row.
When the If statement is satisfied,
1.) puts the current date into column "A" of the next row (determined by j) on sheet "Workable"
2.) puts Sheets("Initial Query Pull").Cells(i, 1).Value into column "B" , on whatever row j is, on whatever sheet is third in your tab line-up.

I suspect both 1 & 2 should both be sheet "Workable", but as written that may not be the case.

Your last paragraph needs some things clarified...
The code needs to account for column 2 for status and then column 3 for name.
Column 2 and 3 of what sheet ?
What does 'account for' mean and how would one know if these are accounted for ?

If these two are found look to column 1 for SampleID
Found where and what's the significance of SampleID ?

and then find the last row (potentially 1,000) that does not state "CLS" in Column 27
Find the last row of what ?

and get information.
What information and do what with it ?

1.) puts the current date into column "A" of the next row (determined by j) on sheet "Workable"
Yes
2.) puts Sheets("Initial Query Pull").Cells(i, 1).Value into column "B" , on whatever row j is, on whatever sheet is third in your tab line-up.
Yes
I suspect both 1 & 2 should both be sheet "Workable", but as written that may not be the case.
Workable is the landing worksheet
Your last paragraph needs some things clarified...
The code needs to account for column 2 for status and then column 3 for name.
Column 2 and 3 of what sheet ?
("Initial Query Pull")
What does 'account for' mean and how would one know if these are accounted for ?
Sorry for my wording, I meant I do not have a code right now that allows for the volume of entries I explain in my first post. My code at this point will find the first instance and use that first entry for checking the row where it is found and copying the value in cells in that row.
If these two are found look to column 1 for SampleID
Found where and what's the significance of SampleID ?
SampleID is the sample number for the multiple entities that are listed I wrote about above. Column 2 is the first argument and divides the potential list of all SampleIDs in half, then column 3 narrows to the Technician assigned. If the column 3 and 2 have the correct values, look for the SampleID in column 1
and then find the last row (potentially 1,000) that does not state "CLS" in Column 27
Find the last row of what ?
The last row of the SampeID entries which could be as many as 1000 rows for each SampleID. Some of the rows will have been closed ("CLS") for their tasks and I want the last task row that is not "CLS" in column 27.
and get information.
What information and do what with it ?
The rows that will be copied into "Workables" sheet table. (i.e., Sheets("Workables").Cells(j, 2) = Sheets("Initial Query Pull").Cells(i, 1).Value

Does hat help?

DThib

 

[NoSparks]

Re: Extremely complex puzzle to find result in a worksheet and use to copy information in another

My code at this point will find the first instance and use that first entry for checking the row where it is found and copying the value in cells in that row.

What would distinguish one found instance from another and indicate the instance being for the current "Initial Query Pull" row or not ?

 

[DThib]

Re: Extremely complex puzzle to find result in a worksheet and use to copy information in another

Hello NoSparks,

The most narrow result would be column (AK). I would like to pull information from the last entry (row) that would be defined by AK.

The other columns we have been talking about will be needed, before AK is called.

DThib

 

[NoSparks]

Re: Extremely complex puzzle to find result in a worksheet and use to copy information in another

Sorry, I give up having to ask everything.

 

[DThib]

Re: Extremely complex puzzle to find result in a worksheet and use to copy information in another

I apologize if I have frustrated you.

It was not my intent.

Basically,
The database pulls information into columns A:AS. The draw pulls every instance of every action for every record, this ends up showing every transaction (about 850 rows per Item #). I am trying to accomplish the sequence below:

Look at Column 2(B: Overall Status) for "Inworks", then see if column 27 (AA: Step Status) is anything but "CLS" and column 20(T: Initiation Date) has a date, and because this narrows this down almost completely, 1 more statement would be column 37 (AK: Group Cause).
The last statement needs to look at the last row meeting all these conditions and pull data for the table from this row.

DThib

 

[DThib]

Re: Extremely complex puzzle to find result in a worksheet and use to copy information in another

Can anyone help me get the last match to produce the the last matched row?
Here is the cod. It returns the instances I need but it returns EVERYTHING!

I need the code to pull the last matched of last argument. Cannot figure it out.

Dim LastRow, SecondRow As Long
  Dim i As Long
  Dim j As Long
  Dim First, Second As String
    
    LastRow = Sheets("Initial Query Pull").Cells(Rows.Count, "A").End(xlUp).Row
    SecondRow = Sheets("Workable").Cells(Rows.Count, "B").End(xlUp).Row
    i = 1 + LastRow
    j = 1 + SecondRow
    First = "Complaint"
    Second = "Review"
    
    For i = 1 To LastRow 'Each i In Sheets("Initial Query Pull")
        If Sheets("Initial Query Pull").Cells(i, 2) = "Drum" And Sheets("Initial Query Pull").Cells(i, 27) _
        <> "Bus" And Sheets("Initial Query Pull").Cells(i, 20) <> "" And (Sheets("Initial Query Pull").Cells(i, 11) _
        = " " Or Sheets("Initial Query Pull").Cells(i, 11) = "") Then
         [COLOR=#b22222][B] If Sheets("Initial Query Pull").Cells(i, 26) = First Or Sheets("Initial Query Pull").Cells(i, 26) = Second[/B][/COLOR] Then

Argument in red.
Code works but I need to pull only the last matching instance.
DThib

 

[NoSparks]

Re: Extremely complex puzzle to find result in a worksheet and use to copy information in another

The more I read this thread the more confusing it is.

I still can't figure out WHAT your goal is or WHAT you're wanting accomplish.

I originally thought this was WHAT you wanted to do, but I don't really know.
1) look at each row of "Initial Query Pull"
2) determine if the row should have its information written to "Workable", and if so
3) write it to the bottom of "Workable", but only if this row hasn't already been previously written to "Workable"

How about sharing an example workbook showing what you're starting with and what you're wanting to end up with.

 

[DThib]

Re: Extremely complex puzzle to find result in a worksheet and use to copy information in another

Sure.

https://app.box.com/s/vmzh0dtm9myeormi67zap7j23z4kje1r

The Macro is Workie.

As you can tell, the entries on "Initial Query Pull" have a lot of similar information in each row.
This is the worksheet that data is pulled from.
It needs to look like the Workable tab entry but from the last row matching the arguments.
If you run the Workie macro, you can see it pulls every row matching the arguments.

DThib