Extract all data from Sheet1 Based on 1 Cell

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
34
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

Order Number

<tbody>
</tbody>
Equipment number/Bar Code

<tbody>
</tbody>
Material code

<tbody>
</tbody>
Parts Qty/ Amount

<tbody>
</tbody>
Part Name/ Material description

<tbody>
</tbody>
Feedback Date

<tbody>
</tbody>
St Loc / CCBPL

<tbody>
</tbody>
Sp Stk

<tbody>
</tbody>
Valuation Type

<tbody>
</tbody>
Price

<tbody>
</tbody>
Month

<tbody>
</tbody>
Amount

<tbody>
</tbody>
Station

<tbody>
</tbody>

<tbody>
</tbody>

Data to be processed at Sheet 2

S.NoComplaint#Order NoDateShop NameAsset codeFault& RamedyPart Change NameLevelTerritoryAmountRemarksAgencyComplaint Fwd DateClosing DateTotal DaysTotal HoursManual LevelMonthJan

<tbody>
</tbody>

Only to pick available information upon Month change.

Best Regards
 

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
34
Any guesses?

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

Order Number

<tbody>
</tbody>
Equipment number/Bar Code

<tbody>
</tbody>
Material code

<tbody>
</tbody>
Parts Qty/ Amount

<tbody>
</tbody>
Part Name/ Material description

<tbody>
</tbody>
Feedback Date

<tbody>
</tbody>
St Loc / CCBPL

<tbody>
</tbody>
Sp Stk

<tbody>
</tbody>
Valuation Type

<tbody>
</tbody>
Price

<tbody>
</tbody>
Month

<tbody>
</tbody>
Amount

<tbody>
</tbody>
Station

<tbody>
</tbody>

<tbody>
</tbody>

Data to be processed at Sheet 2

S.NoComplaint#Order NoDateShop NameAsset codeFault& RamedyPart Change NameLevelTerritoryAmountRemarksAgencyComplaint Fwd DateClosing DateTotal DaysTotal HoursManual LevelMonthJan

<tbody>
</tbody>

Only to pick available information upon Month change.

Best Regards
 

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
34
Hi,

After searching through web regarding the requirement, I found a very close solution in VBA. Now I'm bit stuck with references to month (Validated list in T column)

The code is basically extracting data based on static (Already defined in code) dates. I want the same to use for the month purpose

Code:
Sub extractDataBasedOnDate()
Dim lastrow As Long, erow As Long, i As Long
Dim mydate As Date
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range(“A1”).Select
For i = 2 To lastrow
    mydate = Cells(i, 2)
[B]If mydate >= “14-mar-2012” And mydate <= “20-mar-2012”[/B] Then
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Range(Cells(i, 1), Cells(i, 2)).Copy Destination:=Sheets(“sheet2”).Cells(erow, 1)
End If
Next I
End Sub
Thanking in advance.

Best Regards
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,434
Office Version
365
Platform
Windows
Now I'm bit stuck with references to month (Validated list in T column)
Where exactly are these months coming from?
Is it column T of the same row where "mydate" is coming from?
That is only one date, however. What is the other date? Is it always 6 more than the first date?
You should be able to reference them in the code, just like you are referencing cells for "mydate".
 

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
34
Where exactly are these months coming from?
Is it column T of the same row where "mydate" is coming from?
That is only one date, however. What is the other date? Is it always 6 more than the first date?
You should be able to reference them in the code, just like you are referencing cells for "mydate".
Dear Joe,

Thank you for your kind response.

This is base data in sheet named "Material Consuption"
Data in Base sheet can be anywhere from 1st (A:M) Month to be considered for is in "K" Column

Complaint #
(Column A)
Equipment number/Bar CodeMaterial codeParts Qty/ AmountPart Change NameFeedback DateSt Loc / CCBPLSp StkValuation TypePriceMonthAmountService Center
(Column M)

<tbody>
</tbody>

Data to be extracted on Sheet named "Billing" has following format

S.No
(Column A)
Complaint#Order NoDateShop NameAsset codeFault& RamedyPart Change NameLevelTerritoryAmountRemarksAgencyComplaint Fwd DateClosing DateTotal DaysTotal HoursManual LevelMonthJan
(Column T)

<tbody>
</tbody>

Highlighted cells are match in both sheets

Thank you
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,434
Office Version
365
Platform
Windows
I don't think that answers the questions I asked.

I was offering to help you with the VBA code that you said almost works, but needs to be adjusted for month references.
Can you please specifically answer the questions I asked in my previous response?
 
Last edited:

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
34
I don't think that answers the questions I asked.

I was offering to help you with the VBA code that you said almost works, but needs to be adjusted for month references.
Can you please specifically answer the questions I asked in my previous response?
Where exactly are these months coming from?
Is it column T of the same row where "mydate" is coming from? (No MyDate is in Sheet1)
That is only one date, however. What is the other date? Is it always 6 more than the first date? (No, it is a full column having data from Dec 2017 till date and so on)
You should be able to reference them in the code, just like you are referencing cells for "mydate". ( I didn't write this code, I am just trying to learn as I mentioned "After searching through web regarding the requirement, I found a very close solution in VBA"


 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,434
Office Version
365
Platform
Windows
Instead of just headings, can you post some actual data?
Maybe then it will make more sense to us.
 

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
34
Instead of just headings, can you post some actual data?
Maybe then it will make more sense to us.
Dear Joe,

Below is actual data, Month is extracted from date using (=text(cellref,"mmm")

Sheet name (Material Consuption)

I just need to extract data based on Validated list (Column T) in next sheet. If the month changes all the data to related month must be extracted.


ABCDEFGHIJKLM
Order NumberEquipment number/Bar CodeMaterial codeParts Qty/ AmountPart Name/ Material descriptionFeedback DateSt Loc / CCBPLSp StkValuation TypePriceMonthAmountService Center
432007300129869306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432009300204495306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432017300273446306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432018300008058306-990-5911COMPLAINT HANDLING SERVICE WARRANTY22-Oct-17 0.01Oct0.01Center B
432020300376550306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432026300229764306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center B
432027300446307306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432031300293920306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432038300676611306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432044300010056306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center B
432059300005141306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432062300368729306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432063300367512306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432064300271624306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432066300366997306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432074300246216306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432080300352533306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432083300600077306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432125300324218306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432153300313081306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432156300131614306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432159300013229306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center B
432222300354393306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432231300336855306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center B
432242300359989306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432244300439463306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432263300263331306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432268300157558306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432271300157542306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432275300013669306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432277300157723306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432280300454367306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432282300662065306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432628300032991306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432364300134045306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432367300134045306-990-5911COMPLAINT HANDLING SERVICE WARRANTY23-Oct-17 0.01Oct0.01Center B
432371300619240306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432411300202780306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432498300679209306-990-3531Complaint Handling Charges23-Oct-17 315Oct315Center A
432609300369761306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432611300334255306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A
432615300613283306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center B
432618300394026306-990-3531Complaint Handling Charges22-Oct-17 315Oct315Center A

<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
 

Forum statistics

Threads
1,081,748
Messages
5,361,041
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top