SOLVED

Need help Help with Excel

Copper Contributor

Hi all,

i have tried a few things without success so turning to the pros...

 

customer records with data that is left to right as standard.  One column is a yes or no answer.  I need anything with the yes answer to copy into a secondary sheet.  For the life of me, I cannot get this to work

 

columns read left to right as follows:

 

date; name; Speech; Diet; Physio; OT; OT req; Client Type; Location; Comment

 

Speech is the column that’s needs to be copied on a yes field 

 

HELP

8 Replies

@Djrtmum 

What did you try which didn't work?

There are several ways to achieve this, Formulas, Advanced Filter or Macro. Which way would you prefer?

Can you upload a small sample file with the layout as your original file has and mock up the desired output manually on another sheet?

Once the Yes data is transferred to another sheet, would you like to delete it from the original sheet?

@Subodh_Tiwari_sktneer 

 

thanks- you for replying, it must stay on the first sheet as well.  I tried vlookup  and if formula, I keep getting error.  I know it’s reasonable simple, just not quite getting it, so turn to the pros.  

 

ENQUIRY DATENAMESpeechDietPhysioOTOT REQUIREMENTCLIENT TYPELOCATIONSOURCE OF REFERRAL
Offer to Assist
Unable to Assist
DATE PD INPUT
ASANA TASKED
NOTES                
28/8/19PhilYESNONONO PRIVATE PAYToowoombaSIBLING    Tried to call to offer an app after email                
29/8/19LarkinNOYESNONO NDIS AGENCYToowoombaNDIS   Y                 
30/8/19CaitlynNONOYESNO NDIS SELFToowoombaWEBSITE   YAlso first Speech assessment 27/09/2019                
30/8/19WilliamNONONOYES PRIVATE PAYToowoombaWEBSITE   YAlso first Speech Assessment 25/09/2019                
23/8/19RichterYESYESNONOTHERAPY (eg asd etc)DVAToowoombaWORD OF MOUTH    Needing basic OT in Beenleigh                
2/8/19AlvaYESNOYESNO NDIS AGENCYChinchillaHOME INSTEAD

@Djrtmum 

You may try something like this...

Sub CopyDataForYesSpeech()
Dim wsData      As Worksheet
Dim wsOutput    As Worksheet
Dim lr          As Long

Application.ScreenUpdating = False

Set wsData = Worksheets("Sheet1")           'Sheet with Data
lr = wsData.Cells(Rows.Count, 3).End(xlUp).Row
On Error Resume Next
Set wsOutput = Worksheets("Speech-Yes")     'Name of Output Sheet
wsOutput.Cells.Clear
On Error GoTo 0

If wsOutput Is Nothing Then
    Set wsOutput = Worksheets.Add(after:=wsData)
    wsOutput.Name = "Speech-Yes"            'Name of Output Sheet
End If
wsData.AutoFilterMode = False

With wsData.Range("A1").CurrentRegion
    .AutoFilter field:=3, Criteria1:="YES"
    .SpecialCells(xlCellTypeVisible).Copy wsOutput.Range("A1")
    wsData.Range("C2:C" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With

wsOutput.UsedRange.Columns.AutoFit
wsOutput.Select
Application.ScreenUpdating = True
End Sub

 

In the attached, click on the button called "Transfer Data" on Sheet1 to run the code.

The code will transfer all the data from Sheet1 to a new sheet called "Speech-Yes". You may change the name of the Data and Output Sheets as per your requirement.

 

 

 

@Subodh_Tiwari_sktneer 

 

Thanks so much, I have changed it slightly but cannot find the removal for deleting records.  I need to keep all records with the transfer of speech records to move to the other sheet.  No deletion.

 

You assistance is very much appreciated 

 

@djrtmum490 

Please rephrase your requirement as it is not clear.

Let me know which assumption is correct?

 

  1. Once the data from mock intake Sheet, where column C has Yes, is transferred to Speech-Yes Sheet, you don't want to delete the data from mock intake Sheet which was transferred to Speech-Yes Sheet.
  2. You want to transfer the data from mock intake Sheet to the Speech-Yes" Sheet but don't want to delete the existing data on Speech-Yes Sheet and copy the data below the existing data on Speech-Yes Sheet. And delete the data from mock intake which got transferred to the Speech-Yes Sheet.

You could simple prepare the sample file with the following sheets to show the end result you are trying to achieve...

 

Sheet1 (mock intake):

This should be the sheet with the original data before the macro runs.

 

Sheet2 (mock intake After):

This should be the sheet which should contain the data after the macro is runs.

 

Sheet3 (Speech-Yes Before):

This should be the sheet with the existing data if any before the macro runs.

 

Sheet4 (Speech-Yes After):

This should be the sheet with the transferred data after the macro runs.

 

If required upload a sample workbook with the sheets as mentioned above but data should not be more than 10 rows.

@Subodh_Tiwari_sktneer 

 

sorry about the extra lines, I was adding data to check through. Otherwise, don't worry about my last... worked it out

 

It wasnt deleting it was sorting. 

 

Thank-you so very much

@djrtmum490 

You're welcome! Glad it worked as desired.

Please take a minute to accept the post with the proposed solution as a Best Response/Answer to mark your question as Solved.

best response confirmed by Djrtmum (Copper Contributor)
Solution

@djrtmum490 

To mark a post as a Best Response, you will find a button called Mark as Best Response below the post and you only need to click that button.

Please refer to following image...

Best Response.jpg

1 best response

Accepted Solutions
best response confirmed by Djrtmum (Copper Contributor)
Solution

@djrtmum490 

To mark a post as a Best Response, you will find a button called Mark as Best Response below the post and you only need to click that button.

Please refer to following image...

Best Response.jpg

View solution in original post