How to extract new sheet of unique data from another sheet in a workbook using VBA Excel

[s ganesh]

Hi All,

I am using Excel 2007. I am trying to prepare new a Excel sheet which will replace a conventional white board usage in my company.Btw I'm new to Excel VBA. Ours is a car rental company we need to track where our vehicles are daily at the end of the day.So this sheet will be updated with a user form at the start of the day for the transit happened on previous day.

What I need:
I have a work book with three sheets. Sheet1 will be displayed when excel is opened. Its the main sheet which will look exactly like the White board with customer name in the 1 st Column(A2 to A20) and car numbers in the range B2 to Q20. It will look something like this.
Sheet1
[TABLE="class: outer_border, width: 300, align: left"]
<tbody>[TR]
[TD]Customer Name[/TD]
[TD]Car numbers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]1234[/TD]
[TD]3214[/TD]
[TD]4661[/TD]
[TD]6548[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]5216[/TD]
[TD]6546[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]4561[/TD]
[TD]8462[/TD]
[TD][/TD]
[TD]6464[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD]2465[/TD]
[TD][/TD]
[TD]1642[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2[TABLE="class: outer_border, width: 300, align: center"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Driver[/TD]
[TD]Customer[/TD]
[TD]Car numbers[/TD]
[TD]TIme[/TD]
[/TR]
[TR]
[TD]8/8/14[/TD]
[TD]ABC[/TD]
[TD]Customer1[/TD]
[TD]3214[/TD]
[TD]9:00[/TD]
[/TR]
[TR]
[TD]9/8/14[/TD]
[TD]ABG[/TD]
[TD]Customer2[/TD]
[TD]2451[/TD]
[TD]10:00[/TD]
[/TR]
[TR]
[TD]8/8/14[/TD]
[TD]GFD[/TD]
[TD]Customer1[/TD]
[TD]5663[/TD]
[TD]5:00[/TD]
[/TR]
[TR]
[TD]9/8/14[/TD]
[TD]GHF[/TD]
[TD]Customer3[/TD]
[TD]4561[/TD]
[TD]6:00[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 3 is look up list. It has values which forms the named ranges and is used get values in sheet2.

The data in sheet 2 is obtained with a user form with many combo box and text boxes using Excel VBA.
My question is how extract data from sheet 2 Like what I have in Sheet 1 using EXcel VBA code. I want it to be sorted for individual days with customer in Column 1 and the vehicles arranged accordingly based on the date.

Thanks for your help.

Ganesh

 

[MickG]

It would help if sheet 1 reflected sheet2 !!

I want it to be sorted for individual days with customer in Column 1 and the vehicles arranged accordingly based on the date.

Ref The above:-
In sheet 1 there are Blank spaces in the data, I imagine to reflect specific dates per column, please clarify, although there are no dates shown in sheet1
Do you want the Dates to be shown in sheet1 ???? if yes, Example please
In sheet2 "Customer1" is shown twice on the same date. Where does that customer go on sheet1. ????
Are the dates per Customer, in sheet2 shown chronologically. ????

 

[s ganesh]

Dear MickG,

Thank you for your reply. I'm sorry I guess I haven't conveyed what I need clearly. I attached the tables just to convey the idea, the values given are irrelevant. Consider the sheets given below and void the tables given earlier.

As I had mentioned earlier the sheet 2 is generated with the help of named ranges in sheet 3 and a user form to enter the details in sheet2.
Sheet 2 will look like this.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Driver Name[/TD]
[TD]Date[/TD]
[TD]Car No.[/TD]
[TD]Start From[/TD]
[TD]End At[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]8/8/14[/TD]
[TD]1111[/TD]
[TD]CUSTOMER1[/TD]
[TD]CUTOMER2[/TD]
[TD]9:00 am[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]8/8/14[/TD]
[TD]1112[/TD]
[TD]CUSTOMER2[/TD]
[TD]CUSTOMER3[/TD]
[TD]6:00am[/TD]
[/TR]
[TR]
[TD]CCC [/TD]
[TD]8/8/14[/TD]
[TD]1113[/TD]
[TD]CUSTOMER3[/TD]
[TD]CUSTOMER1[/TD]
[TD]5:00pm[/TD]
[/TR]
[TR]
[TD]DDD [/TD]
[TD]8/8/14[/TD]
[TD]1111[/TD]
[TD]CUSTOMER2[/TD]
[TD]CUSTOMER4[/TD]
[TD]4:00pm[/TD]
[/TR]
[TR]
[TD]EEE[/TD]
[TD]8/8/14[/TD]
[TD]1112[/TD]
[TD]CUSTOMER3[/TD]
[TD]CUSTOMER1[/TD]
[TD]6:00pm[/TD]
[/TR]
[TR]
[TD]FFF[/TD]
[TD]8/8/14[/TD]
[TD]1114[/TD]
[TD]CUSTOMER4[/TD]
[TD]CUSTOMER3[/TD]
[TD]9:00pm[/TD]
[/TR]
[TR]
[TD]DDD[/TD]
[TD]9/9/14[/TD]
[TD]1113[/TD]
[TD]CUSTOMER1[/TD]
[TD]CUSTOMER2[/TD]
[TD]10:00am[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]9/9/14[/TD]
[TD]1111[/TD]
[TD]CUSTOMER4[/TD]
[TD]CUSTOMER1[/TD]
[TD]11:00am[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]9/9/14[/TD]
[TD]1112[/TD]
[TD]CUSTOMER1[/TD]
[TD]CUSTOMER3[/TD]
[TD]10:00am[/TD]
[/TR]
[TR]
[TD]EEE[/TD]
[TD]9/9/14[/TD]
[TD]1113[/TD]
[TD]CUSTOMER2[/TD]
[TD]CUSTOMER4[/TD]
[TD]5:00pm[/TD]
[/TR]
[TR]
[TD]FFF[/TD]
[TD]9/9/14[/TD]
[TD]1114[/TD]
[TD]CUSTOMER3[/TD]
[TD]CUSTOMER2[/TD]
[TD]1:00pm[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]9/9/14[/TD]
[TD]1114[/TD]
[TD]CUSTOMER2[/TD]
[TD]CUSTOMER4[/TD]
[TD]5:00pm[/TD]
[/TR]
</tbody>[/TABLE]






















As you can see from the above sheet , One car travels to one or more customer's place in a day.

The sheet 1 should have a command button(Active Xcontrol) in the name say "Get detail on ____ date", when the date is entered in the ____ and command button is pressed. Sheet 1 should look like something like this,
So when 8/8/14 is entered in the ___ and Command button is pressed, sheet1 will like below

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Customer Name[/TD]
[TD]Car No.s[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]CUSTOMER1[/TD]
[TD]1112,1113[/TD]
[TD]8/8/14[/TD]
[/TR]
[TR]
[TD]CUSTOMER2[/TD]
[TD][/TD]
[TD]8/8/14[/TD]
[/TR]
[TR]
[TD]CUSTOMER3[/TD]
[TD]1114[/TD]
[TD]8/8/14[/TD]
[/TR]
[TR]
[TD]CUSTOMER4[/TD]
[TD]1111[/TD]
[TD]8/8/14[/TD]
[/TR]
</tbody>[/TABLE]









So when 9/8/14 is entered in the ___ and Command button is pressed, sheet1 will like below

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Customer Name[/TD]
[TD]Car No.s[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]CUSTOMER1[/TD]
[TD]1111[/TD]
[TD]9/8/14[/TD]
[/TR]
[TR]
[TD]CUSTOMER2[/TD]
[TD][/TD]
[TD]9/8/14[/TD]
[/TR]
[TR]
[TD]CUSTOMER3[/TD]
[TD]1112[/TD]
[TD]9/8/14[/TD]
[/TR]
[TR]
[TD]CUSTOMER4[/TD]
[TD]1113,1114[/TD]
[TD]9/8/14[/TD]
[/TR]
</tbody>[/TABLE]









I think the 2 tables makes some sense.In short the sheet1 should show the car's last location at the end of the day(whichever is chosen).The sheet should have a text box where date can be entered and once the command button is pressed result should be generated. I am not sure if this possible.

Need some help of the experts here to solve this problem. It would be great if some one could help me with this.

Thank in advance.

Gans

 

[MickG]

This code runs from a Command Button as Required:_
The Dates from sheet2 are listed in "D1" (As Validation List) when you run the code in sheet1.
The results are based on your selection from "D1".
NB:- This code could run Totally from the selection of the date in "D1" if required.

Private [COLOR=Navy]Sub[/COLOR] CommandButton1_Click()
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dic         [COLOR=Navy]As[/COLOR] Object
[COLOR=Navy]Dim[/COLOR] K
[COLOR=Navy]Dim[/COLOR] c           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] G           [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Str         [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] Stg         [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] MyDt [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]With[/COLOR] Sheets("Sheet2")
    [COLOR=Navy]Set[/COLOR] Rng = .Range(.Range("D2"), .Range("D" & Rows.Count).End(xlUp))
[COLOR=Navy]End[/COLOR] With
        [COLOR=Navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
            Dic.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Offset(, -2):Dic.Item(Dn.Value) = Empty:[COLOR=Navy]Next[/COLOR] Dn

[COLOR=Navy]With[/COLOR] Range("D1").Validation
      .Delete
     .Add Type:=xlValidateList, Formula1:=Join(Dic.keys, ",")
[COLOR=Navy]End[/COLOR] With
Dic.RemoveAll

[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        Dic.Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] Dic.Item(Dn.Value) = Union(Dic.Item(Dn.Value), Dn)
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]With[/COLOR] Sheets("Sheet1")
  MyDt = .Range("D1").Value
    .Range("A:C").ClearContents
    [COLOR=Navy]If[/COLOR] MyDt = "" [COLOR=Navy]Then[/COLOR]
            MsgBox "Please [COLOR=Navy]Select[/COLOR] Date from ""D1""": .Range("A:C").ClearContents
            .Range("A1").Resize(, 3) = Array("Customer Name", "Car No.s", "Date")
            [COLOR=Navy]Exit[/COLOR] [COLOR=Navy]Sub[/COLOR]
    [COLOR=Navy]End[/COLOR] If
    
c = 1
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] Dic.keys
    c = c + 1
    .Cells(c, 1) = K
        [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] G [COLOR=Navy]In[/COLOR] Dic.Item(K)
           [COLOR=Navy]If[/COLOR] DateValue(G.Offset(, -2).Value) = DateValue(MyDt) [COLOR=Navy]Then[/COLOR]
               Str = Str & ", " & G.Offset(, -1)
           [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]Next[/COLOR] G
            .Cells(c, 2) = Mid(Str, 2): Str = ""
            .Cells(c, 3) = MyDt
[COLOR=Navy]Next[/COLOR] K
    .Range("A1").Resize(, 3) = Array("Customer Name", "Car No.s", "Date")
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[s ganesh]

Dear Mick,

This works perfectly.I had been struggling with this for the past one week .
Thank you so much.


Regards
Gans