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

Customer Name	Car numbers
Customer 1	1234	3214	4661	6548
Customer 2	5216	6546
Customer 3	4561	8462		6464
Customer 4	2465		1642

<tbody>
</tbody>

Sheet 2

Date	Driver	Customer	Car numbers	TIme
8/8/14	ABC	Customer1	3214	9:00
9/8/14	ABG	Customer2	2451	10:00
8/8/14	GFD	Customer1	5663	5:00
9/8/14	GHF	Customer3	4561	6:00

<tbody>
</tbody>

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.

Driver Name	Date	Car No.	Start From	End At	Time
AAA	8/8/14	1111	CUSTOMER1	CUTOMER2	9:00 am
BBB	8/8/14	1112	CUSTOMER2	CUSTOMER3	6:00am
CCC	8/8/14	1113	CUSTOMER3	CUSTOMER1	5:00pm
DDD	8/8/14	1111	CUSTOMER2	CUSTOMER4	4:00pm
EEE	8/8/14	1112	CUSTOMER3	CUSTOMER1	6:00pm
FFF	8/8/14	1114	CUSTOMER4	CUSTOMER3	9:00pm
DDD	9/9/14	1113	CUSTOMER1	CUSTOMER2	10:00am
CCC	9/9/14	1111	CUSTOMER4	CUSTOMER1	11:00am
AAA	9/9/14	1112	CUSTOMER1	CUSTOMER3	10:00am
EEE	9/9/14	1113	CUSTOMER2	CUSTOMER4	5:00pm
FFF	9/9/14	1114	CUSTOMER3	CUSTOMER2	1:00pm
CCC	9/9/14	1114	CUSTOMER2	CUSTOMER4	5:00pm

<tbody>
</tbody>

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

Customer Name	Car No.s	Date
CUSTOMER1	1112,1113	8/8/14
CUSTOMER2		8/8/14
CUSTOMER3	1114	8/8/14
CUSTOMER4	1111	8/8/14

<tbody>
</tbody>

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

Customer Name	Car No.s	Date
CUSTOMER1	1111	9/8/14
CUSTOMER2		9/8/14
CUSTOMER3	1112	9/8/14
CUSTOMER4	1113,1114	9/8/14

<tbody>
</tbody>

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