VBA code to filter, text2column and copy data

[bombo]

Hi everyone,

I need VBA code to filter, text2column, copy and paste processed data in a new sheet.

I need to return text in Question 1, question 2,... columns into number, then filter rows which in Group column is "ABC", and copy - paste these processed data in a new sheet. New sheet will include column Group, Role, Question 1, 2, 3,... (after split text). I also need a textbox to ask which group I want to filter.

My sample data is as below:

Group	Role	Question 1	Open question 1	Question 2	Open question 2	Question 3	Open question 3
ABC <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>	1	3 - Neutral	NA	4 - Satisfied	NA	3 - Neutral	NA
XYZ	2	4 - Satisfied	NA	3 - Neutral	NA	4 - Satisfied	NA
ABC	3	5 - Very satisfied	NA	4 - Satisfied	NA	4 - Satisfied	NA
ABC	1	3 - Neutral	NA	3 - Neutral	NA	3 - Neutral	NA
GHI	1	5 - Very satisfied	NA	3 - Neutral	NA	3 - Neutral	NA
ABC	2	3 - Neutral	NA	3 - Neutral	NA	3 - Neutral	NA
XYI	3	5 - Very satisfied	NA	3 - Neutral	NA	3 - Neutral	NA
HIQ	3	4 - Satisfied	NA	3 - Neutral	NA	5 - Very satisfied	NA
ABC	3	4 - Satisfied	NA	3 - Neutral	NA	5 - Very satisfied	NA

<tbody>
</tbody>

Please help me. Thank you!

 

[Fluff]

Hi, welcome to the board.
Does this do what you're after?

Sub SplitColFltrCopy()
    
    Dim UsdRws As Long
    Dim Sht As Worksheet
    Dim Col As Long
    Dim Grp As String
    
    Grp = InputBox("Please enter a group")
    If Len(Grp) = 0 Then Exit Sub
    
    Set Sht = ActiveSheet
    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    
    For Col = 3 To 7 Step 2
        Columns(Col).TextToColumns Destination:=Cells(1, Col), DataType:=xlDelimited, _
           Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 9))
    Next Col
    
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = Grp
    
    If Sht.AutoFilterMode Then Sht.AutoFilterMode = False
    With Sht.Range("A1:H" & UsdRws)
        .AutoFilter field:=1, Criteria1:=Grp
        On Error Resume Next
        .SpecialCells(xlVisible).copy Sheets(Grp).Range("A1")
        On Error GoTo 0
        .AutoFilter
    End With
    
End Sub

 

[bombo]

Hi Fluff,

Thank you for helping me!

This code works for my sample data, but it doesn't work in my real data and I don't know how to change it for suitability.

In my real data, the number or rows and columns is not only 9 rows and 8 columns like sample above. Rows and columns are different in each file while the structure is the same. (For example, there are many question and open question columns)

Could you please show me how can I change the code? I also need to convert choice "0 - Do not use services" in Question Columns into "NA" instead of "0".

While applying the code into my file, it said debug in those lines:

Columns(Col).TextToColumns Destination:=Cells(1, Col), DataType:=xlDelimited, _
Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 9))

Thank you!

 

[Rick Rothstein]

This code works for my sample data, but it doesn't work in my real data and I don't know how to change it for suitability.

A Generalized "Please Note"
--------------------------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[Fluff]

@bombo
Could you please supply a sample of your real data showing both before & after results?
A varying number of rows & columns can be dealt with, but (as pointed out by Rick Rothstein) without a sample of your genuine data, there is not much we can do to help.

 

[bombo]

@Rick Rothstein: Noted with many thanks.
@Fluff: My actual data is like table below. I have many files which have the same structure like this, the difference is the number of column and row.

I need to:

1. Convert text from Question columns into number, expect choice "0 - Do not use services" into "NA" instead of "0"
2. Filter rows which in Group column is "ABC" (open text box to ask which group needed to be filtered)
3. Copy - paste these processed data in a new sheet

RESPONSE <tbody> </tbody>	SUBMITTED <tbody> </tbody>	USERNAME <tbody> </tbody>	GROUP <tbody> </tbody>	ROLE <tbody> </tbody>	Question 1	Open question 1	Question 2	Open question 2	Question n	Open question n
1	11/10/2017	fdgwg	ABC	1	3 - Neither Agree nor Disagree <tbody> </tbody>	NA	5 - Strongly agree	NA	5 - Strongly agree	NA
2	11/10/2017	rgwr	XYZ	2	4 - Agree <tbody> </tbody>	NA	3 - Neither Agree nor Disagree	NA	3 - Neither Agree nor Disagree	NA
3	11/10/2017	dgrwf	ABC	3	0 - Do not use services	NA	3 - Neither Agree nor Disagree	NA	0 - Do not use services	NA
4	11/10/2017	dgwrg	JIE	3	5 - Strongly agree <tbody> </tbody>	NA	0 - Do not use services	NA	4 - Agree	NA
5	11/10/2017	gwrfe	ABC	1	5 - Strongly agree <tbody> </tbody>	NA	5 - Strongly agree	NA	3 - Neither Agree nor Disagree	NA
6	11/10/2017	wrfew	ELO	3	5 - Strongly agree <tbody> </tbody>	NA	4 - Agree	NA	4 - Agree	NA
7	11/10/2017	wrfewd	ABC	2	5 - Strongly agree	NA	4 - Agree	NA	4 - Agree	NA
8	11/10/2017	gwrgww	GEW	1	5 - Strongly agree <tbody> </tbody>	NA	4 - Agree	NA	4 - Agree	NA
n	11/10/2017	rgwwds	ABC	1	5 - Strongly agree <tbody> </tbody>	NA	4 - Agree	NA	4 - Agree	NA

<tbody>
</tbody>

 

[Fluff]

Assuming that the first 5 columns are always consistent, try

Sub SplitColFltrCopy()
    
    Dim Usdrws As Long
    Dim UsdCols As Long
    Dim Sht As Worksheet
    Dim col As Long
    Dim Grp As String
    
    Grp = InputBox("Please enter a group")
    If Len(Grp) = 0 Then Exit Sub
    
    Set Sht = ActiveSheet
    Usdrws = Range("A" & Rows.Count).End(xlUp).Row
    UsdCols = Cells(1, Columns.Count).End(xlToLeft).column

    For col = 6 To UsdCols Step 2
        Columns(col).TextToColumns Destination:=Cells(1, col), DataType:=xlDelimited, _
           Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 9))
        Columns(col).Replace 0, "NA", xlWhole, , , False, False
    Next col
    
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = Grp
    
    If Sht.AutoFilterMode Then Sht.AutoFilterMode = False
    With Sht.Range("A1", Sht.Cells(Usdrws, UsdCols))
        .AutoFilter field:=4, Criteria1:=Grp
        On Error Resume Next
        .SpecialCells(xlVisible).copy Sheets(Grp).Range("A1")
        On Error GoTo 0
        .AutoFilter
    End With
    
End Sub

 

[bombo]

@Fluff: Thank you so much. You help me saving a lot of time

 

[Fluff]

Glad to help & thanks for the feedback