Need Solution

[yasirsaeed2002]

Here is an example source table:

Customer	01/12/15	02/12/15	03/12/15	04/12/15	05/12/15	06/12/15	07/12/15
	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday
A	2.00	6.00	3.00	1.00	6.00		7.00
B	6.00		4.00		10.00
C	8.00		10.00		3.00
D		4.00	9.00		1.00
E		8.00	3.00	1.00	7.00

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

I need to set up another table with two columns as follows:

Date Customer


Now the since in the source table on 1st of December 2015 there are three entries so the date (01/12/2015) should repeat three times in three different successive rows in the date column and once the date is repeated the required number of time the required number of unique customers should be in the customer column.

I hope I have made myself clear but please feel free to clarify any thing.

Kind Regards
Yasir Saeed

 

[Special-K99]

Post your expected output.

 

[MickG]

Try this for a Guess at results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG17Aug05
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 2)
[COLOR="Navy"]For[/COLOR] Ac = 2 To UBound(Ray, 2)
    [COLOR="Navy"]For[/COLOR] n = 3 To UBound(Ray, 1)
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            nray(c, 1) = Ray(1, Ac)
            nray(c, 2) = Ray(n, 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
 c = c + 1
 [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 2)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[yasirsaeed2002]

Post your expected output.

It should be something like this:

Date Customer
01/12/2015 A
01/12/2015 B
01/12/2015 C
02/12/2015 A
02/12/2015 D
02/12/2015 E
03/12/2015 A
03/12/2015 B
03/12/2015 C
03/12/2015 D
03/12/2015 E

Regards
Yasir

 

[yasirsaeed2002]

Try this for a Guess at results on sheet2.

[COLOR=Navy]Sub[/COLOR] MG17Aug05
[COLOR=Navy]Dim[/COLOR] Ray [COLOR=Navy]As[/COLOR] Variant, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 2)
[COLOR=Navy]For[/COLOR] Ac = 2 To UBound(Ray, 2)
    [COLOR=Navy]For[/COLOR] n = 3 To UBound(Ray, 1)
        [COLOR=Navy]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR=Navy]Then[/COLOR]
            c = c + 1
            nray(c, 1) = Ray(1, Ac)
            nray(c, 2) = Ray(n, 1)
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] n
 c = c + 1
 [COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 2)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks Mick but there is a problem: I am not familiar with VBA.

 

[MickG]

Try this:-

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.
On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet2 should now be updated.
Regrds Mick

 

[yasirsaeed2002]

Try this:-

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.
On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet2 should now be updated.
Regrds Mick

Thanks Mick, I ll try this.

Kind Regards
Yasir

 

[yasirsaeed2002]

Sub MG17Aug05()Dim Ray As Variant, n As Long, Ac As Long, c As Long
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 2)
For Ac = 2 To UBound(Ray, 2)
For n = 3 To UBound(Ray, 1)
If Not IsEmpty(Ray(n, Ac)) Then
c = c + 1
nray(c, 1) = Ray(1, Ac)
nray(c, 2) = Ray(n, 1)
End If
Next n
c = c + 1
Next Ac
With Sheets("Sheet2").Range("A1").Resize(c, 2)
.Value = nray
.Borders.Weight = 2
.Columns.AutoFit
End With
End Sub


Mick, the bold part is returning an error.

 

[MickG]

Try comparing your data with this example showing the results on sheet2.
https://app.box.com/s/chl9d8qnx39wb7kvj9t719y50czcrtq0

 

[yasirsaeed2002]

Try comparing your data with this example showing the results on sheet2.
https://app.box.com/s/chl9d8qnx39wb7kvj9t719y50czcrtq0

Sub MG17Aug05
Dim Ray As Variant, n As Long, Ac As Long, c As Long
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 2)
For Ac = 2 To UBound(Ray, 2)
For n = 3 To UBound(Ray, 1)
If Not IsEmpty(Ray(n, Ac)) Then
c = c + 1
nray(c, 1) = Ray(1, Ac)
nray(c, 2) = Ray(n, 1)
End If
Next n
c = c + 1
Next Ac
With Sheets("Sheet2").Range("A1").Resize(c, 2)
.Value = nray
.Borders.Weight = 2
.Columns.AutoFit
End With
End Sub
Hi Mick still the bold part is causing problem. It says type mismatch 13 and debugging highlights the bold part.

Would it matter if I create the module while I am on the sheet which has the data or do I need to create the 2nd worksheet first?

Kind Regards
Yasir Saeed