Feeding multiple client sales into new tab

[Peterball]

Hi,

This may have been answered before.

I am looking to develop a excel system where I can enter the customers name and information on the first tab along with their sale item and then this information will feed through to a new tab based on the customers name.

If i had the below list
[TABLE="width: 478"]
<colgroup><col><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Account Name[/TD]
[TD]Date[/TD]
[TD] [/TD]
[TD]Product[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Peter[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD] [/TD]
[TD]Hat[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Paul[/TD]
[TD="align: right"]06/01/2016[/TD]
[TD] [/TD]
[TD]Scarf[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]David[/TD]
[TD="align: right"]15/01/2016[/TD]
[TD] [/TD]
[TD]Hat[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Steven[/TD]
[TD="align: right"]03/02/2016[/TD]
[TD] [/TD]
[TD]Gloves[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Peter[/TD]
[TD="align: right"]06/02/2016[/TD]
[TD] [/TD]
[TD]Gloves[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]David[/TD]
[TD="align: right"]17/02/2016[/TD]
[TD] [/TD]
[TD]Scarf[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Peter[/TD]
[TD="align: right"]20/02/2016[/TD]
[TD] [/TD]
[TD]Socks[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Steven[/TD]
[TD="align: right"]25/02/2016[/TD]
[TD] [/TD]
[TD]Hat[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
</tbody>[/TABLE]



I would then want this information to feed to a separate tab for each person.
I can find the information using VLOOK up but then it will not show multiple items.

I would like the next tab to populate for Peter as per below
[TABLE="width: 365"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Account Name[/TD]
[TD]Date[/TD]
[TD] [/TD]
[TD]Product[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD] [/TD]
[TD]Hat[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]06/02/2016[/TD]
[TD] [/TD]
[TD]Gloves[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]20/02/2016[/TD]
[TD] [/TD]
[TD]Socks[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]


If I then had a new sale, I ad it to the main tab and it populates the other tabs for me.

Thanks in advance for your help.

Regards,
Peter

 

[godsaaint]

Easier way to do this would be to use a pivot table, which will summarize the data by names for you. Check this link for reference if needed:

https://support.office.com/en-us/ar...eet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

If for some reason, you need anyways a sheet/tab for each person, PivotTables have a "Show Report Filter Pages" that can do this for you. In this case you would only need to have the Account Names in the filter area of the pivottable, and the rest of the columns in the rows area.

 

[Peterball]

Easier way to do this would be to use a pivot table, which will summarize the data by names for you. Check this link for reference if needed:

https://support.office.com/en-us/ar...eet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

If for some reason, you need anyways a sheet/tab for each person, PivotTables have a "Show Report Filter Pages" that can do this for you. In this case you would only need to have the Account Names in the filter area of the pivottable, and the rest of the columns in the rows area.

Hi,

I have tried this but am still having issues.

The pivot table will show a sum of the information but I want this t create a list automatically on the next page.

This will be used to compile a list of transactions that can be sent to the customer.

That is why I was hoping that the information would feed into the new tab one after the other.

I am not sure how to go about this with Pivot Table.

Thanks sorry im not more adapt at this but I appreciate the help.

Regards,
Peter

 

[FDibbins]

Try this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Account Name[/td][td]Date[/td][td][/td][td]Product[/td][td]Price[/td][/tr]

[tr][td]

2​

[/td][td]Peter[/td][td]

1/1/2016​

[/td][td]0[/td][td]Hat[/td][td]

2.5​

[/td][/tr]

[tr][td]

3​

[/td][td]Peter[/td][td]

6/2/2016​

[/td][td]0[/td][td]Gloves[/td][td]

3.5​

[/td][/tr]

[tr][td]

4​

[/td][td]Peter[/td][td]

20/02/2016​

[/td][td]0[/td][td]Socks[/td][td]

4​

[/td][/tr]
[/table]
A2=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$B$2:$B$9="Peter",ROW(Sheet1!$B$2:$B$9)),ROWS($A$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter

Note that I manually entered Peter, but if you need to pull the sheet name, you could put this in a helper cell somewhere and then reference that...
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

 

[MickG]

Try this:-
NB:- This code will create sheets as per names in column "A" sheet1 and fill accordingly.

[COLOR="Navy"]Sub[/COLOR] MG14Dec25
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, Sht [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            ReDim ray(1 To 4, 1 To 2)
            ray(1, 1) = "Account Name": ray(2, 1) = "Date": ray(3, 1) = "Product": ray(4, 1) = "Price"
            [COLOR="Navy"]For[/COLOR] Ac = 1 To 4
            [COLOR="Navy"]If[/COLOR] Ac = 2 [COLOR="Navy"]Then[/COLOR]
                ray(Ac, 2) = CDbl(DateValue(Dn(, Ac)))
            [COLOR="Navy"]Else[/COLOR]
                ray(Ac, 2) = Dn(, Ac)
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
            .Add Dn.Value, ray
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Dn.Value)
                ReDim Preserve Q(1 To 4, 1 To UBound(Q, 2) + 1)
                [COLOR="Navy"]For[/COLOR] Ac = 1 To 4
                    [COLOR="Navy"]If[/COLOR] Ac = 2 [COLOR="Navy"]Then[/COLOR]
                        Q(Ac, UBound(Q, 2)) = CDbl(DateValue(Dn(, Ac)))
                    [COLOR="Navy"]Else[/COLOR]
                        Q(Ac, UBound(Q, 2)) = Dn(, Ac)
                    [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]Next[/COLOR] Ac
            .Item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
Application.ScreenUpdating = False
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
     Application.DisplayAlerts = False
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
        Sheets(K).Delete
         [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] GoTo 0
     Application.DisplayAlerts = True
        Sheets.Add(after:=Sheets(Sheets.Count)).Name = K
        [COLOR="Navy"]Set[/COLOR] Sht = ActiveSheet
        Sht.Range("A1").Resize(UBound(.Item(K), 2), 4).Value = Application.Transpose(.Item(K))
        Sht.Range("B:B").NumberFormat = "dd/mm/yyyy"
        [COLOR="Navy"]With[/COLOR] Sht.Range("A1").Resize(UBound(.Item(K), 2), 4)
            .Borders.Weight = 2
            .Columns.AutoFit
        [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] K
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick