Extract all data from Sheet1 Based on 1 Cell

[mohdamir1989]

Hi

I am trying to pull data from other sheet based on single cell (Month) reference. Just like a list in one cell. Sheet 1 contains multiple entries segregated with month column.

I need it all in a different format which I need to grab with single validated list.

Any possibilities??

Sheet 1 Format

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD]Order Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]Equipment number/Bar Code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 264"]
<tbody>[TR]
[TD]Material code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD]Parts Qty/ Amount[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 272"]
<tbody>[TR]
[TD]Part Name/ Material description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD]Feedback Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD]St Loc / CCBPL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 55"]
<tbody>[TR]
[TD]Sp Stk[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD]Valuation Type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD]Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Amount[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD]Station[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Data to be processed at Sheet 2

[TABLE="class: grid, width: 2524, align: center"]
<tbody>[TR]
[TD="width: 55"]S.No[/TD]
[TD="width: 85"]Complaint#[/TD]
[TD="width: 88"]Order No[/TD]
[TD="width: 79"]Date[/TD]
[TD="width: 145"]Shop Name[/TD]
[TD="width: 108"]Asset code[/TD]
[TD="width: 257"]Fault& Ramedy[/TD]
[TD="width: 317"]Part Change Name[/TD]
[TD="width: 62"]Level[/TD]
[TD="width: 94"]Territory[/TD]
[TD="width: 110"]Amount[/TD]
[TD="width: 272"]Remarks[/TD]
[TD="width: 251"]Agency[/TD]
[TD="width: 207"]Complaint Fwd Date[/TD]
[TD="width: 74"]Closing Date[/TD]
[TD="width: 64"]Total Days[/TD]
[TD="width: 64"]Total Hours[/TD]
[TD="width: 64"]Manual Level[/TD]
[TD="width: 64"]Month[/TD]
[TD="width: 64"]Jan[/TD]
[/TR]
</tbody>[/TABLE]

Only to pick available information upon Month change.

Best Regards

 

[Joe4]

I just need to extract data based on Validated list (Column T)

What row in column T does this value appear in?
What format is the value in (is it a Date, is it Text)?

What is the first row on your other sheet the first line of data should be extracted to?

In an earlier post, you said these are the fields to be copied:
- Complaint # (column A)
- Equipment number/Bar Code (column B)
- Part Change Name (column E)
- Amount (column L)

And they should be pasted to these fields on your other sheet:
- Complaint # (column B)
- Asset code (column F)
- Part Change Name (column H)
- Territory (column J)
- Amount (column K)

The number of fields do not match (4 to 5), and it is not clear what the ones highlighted in red font above are matching up with.

 

[mohdamir1989]

What row in column T does this value appear in? Column T is referring to a list of Months (Jan~Dec) a validated manual list can be referred to Column K in sample data
What format is the value in (is it a Date, is it Text)? it is Text but either is acceptable (Column K Month (Text) Column F Feedback Date (Date)

What is the first row on your other sheet the first line of data should be extracted to? Cell (B2) is the first line for data extraction

In an earlier post, you said these are the fields to be copied:
- Complaint # (column A)
- Equipment number/Bar Code (column B)
- Part Change Name (column E)
- Amount (column L)

And they should be pasted to these fields on your other sheet:
- Complaint # (column B)
- Asset code (column F) = - Equipment number/Bar Code (column B)
- Part Change Name (column H)
- Territory (column J) = Service Center
- Amount (column K)

The number of fields do not match (4 to 5), and it is not clear what the ones highlighted in red font above are matching up with.

I hope I could explain the queries well.

I really appreciate your time and efforts towards guiding me. Really thank you so very much.

 

[Joe4]

Try this code:

Sub ExtractData()

    Dim src As Worksheet
    Dim dst As Worksheet
    Dim lRow As Long
    Dim myRow As Long
    Dim dstRow As Long
    Dim mnth As String
    Dim dr As Long
    
    Application.ScreenUpdating = False
    
'   Specify source and destination worksheets
    Set src = Sheets("Material Consumption")
    Set dst = Sheets("Billing")
    
'   Clear all rows on destination sheet (of any prior data), leaving only header
    dr = dst.Range("A1").SpecialCells(xlLastCell).Row
    If dr > 1 Then dst.Rows("2:" & dr).ClearContents
    
'   Set default value of dstRow
    dstRow = 1
    
'   Get month from cell T1 on Destination sheet
    mnth = dst.Range("T1")
    
'   Find last row with data on src sheet (using column A)
    lRow = src.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows on source file starting on row 2
    For myRow = 2 To lRow
'       Check to see if month in column K of data matches month on T1 of destination sheet
        If src.Cells(myRow, "K") = mnth Then
'           Increment row counter
            dstRow = dstRow + 1
'           Populate appropriate cells
            dst.Cells(dstRow, "B") = src.Cells(myRow, "A")
            dst.Cells(dstRow, "F") = src.Cells(myRow, "B")
            dst.Cells(dstRow, "H") = src.Cells(myRow, "E")
            dst.Cells(dstRow, "J") = src.Cells(myRow, "M")
            dst.Cells(dstRow, "K") = src.Cells(myRow, "L")
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[mohdamir1989]

Try this code:

Sub ExtractData()

    Dim src As Worksheet
    Dim dst As Worksheet
    Dim lRow As Long
    Dim myRow As Long
    Dim dstRow As Long
    Dim mnth As String
    Dim dr As Long
    
    Application.ScreenUpdating = False
    
'   Specify source and destination worksheets
    Set src = Sheets("Material Consumption")
    Set dst = Sheets("Billing")
    
'   Clear all rows on destination sheet (of any prior data), leaving only header
    dr = dst.Range("A1").SpecialCells(xlLastCell).Row
    If dr > 1 Then dst.Rows("2:" & dr).ClearContents
    
'   Set default value of dstRow
    dstRow = 1
    
'   Get month from cell T1 on Destination sheet
    mnth = dst.Range("T1")
    
'   Find last row with data on src sheet (using column A)
    lRow = src.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows on source file starting on row 2
    For myRow = 2 To lRow
'       Check to see if month in column K of data matches month on T1 of destination sheet
        If src.Cells(myRow, "K") = mnth Then
'           Increment row counter
            dstRow = dstRow + 1
'           Populate appropriate cells
            dst.Cells(dstRow, "B") = src.Cells(myRow, "A")
            dst.Cells(dstRow, "F") = src.Cells(myRow, "B")
            dst.Cells(dstRow, "H") = src.Cells(myRow, "E")
            dst.Cells(dstRow, "J") = src.Cells(myRow, "M")
            dst.Cells(dstRow, "K") = src.Cells(myRow, "L")
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

Dear Joe,

Code is working perfectly awesome. Thank you so very much

Can you please guide me regarding learning of VBA as my mostly work is repetitive and I do record macros to save time. Still sometimes understanding/writing code can save a lot of time.

Thanks again

 

[Joe4]

Code is working perfectly awesome. Thank you so very much

You are welcome!

Can you please guide me regarding learning of VBA as my mostly work is repetitive and I do record macros to save time. Still sometimes understanding/writing code can save a lot of time.

This question comes up often. One of our users has put together a pretty comprehensive list on resources for learning VBA. Take a look at the links in this thread here: https://www.mrexcel.com/forum/gener...estions/1040281-learning-vba-where-start.html