create new tab taking data from one column

Anonymous
2019-08-26T15:07:20+00:00

Hi

I have  a spreadsheet which has many tabs at the bottom for different companies. Sheet1 is the master where all info is recorded on a daily basis, I run the macro and it copies the data to the relevant company tab, all that is ok.

Now I have been asked that a new page is created with just the contents of one column selected. So for instance column G needs to be selected from the master sheet and a new sheet created called MS,Aldi Separation and any entries that have figures to be copied to a new sheet. The list below was done manually by copy and paste from the master sheet to a new sheet added called MSaldi separation

No. Invoice <br><br>Date Company MSAldi Separation
6.0 Year to 30th June 2015
* 30/06/2014 Forty Shillings 11,026.33
6.4 25/07/2014 40 Shillings 3,567.50
TOTAL 14,593.83 14,593.83
5.0 Year to 30th June 2014
5.9 28/02/2014 Bidwells 1,665.48
5.12 31/03/2014 Bidwells 1,798.40
5.14 30/04/2014 Bidwells 2,480.25
5.16 31/05/2014 Bidwells 7,762.62
5.18 11/06/2014 Bidwells 7,200.00
5.21 30/06/2014 Bidwells 8,173.20
6.0 Year to 30th June 2015
6.5 31/07/2014 Bidwells 4,158.45
6.22 31/01/2015 Bidwells 585.00
8.0 Year to 30th June 2017
8.14 28/02/2017 Bidwells 9,899.00
8.23 30/04/2017 Bidwells 3,725.00
8.25 31/05/2017 Bidwells 3,641.91
8.31 30/06/2017 Bidwells 1,493.43
9.0 Year to 30th June 2018
9.5 31/08/2017 Bidwells 575.00
9.6 31/07/2017 Bidwells 870.00
9.10 30/09/2017 Bidwells 533.50
9.0 Year to 30th June 2018
9.27 30/11/2017 Bidwells 350.00
9.38 31/01/2018 Bidwells 1,647.00
9.0 Year to 30th June 2018
9.46 28/02/2018 Bidwells 1,192.00
9.47 31/03/2018 Bidwells 1,416.00
10.00 Year to 30th June 2019
10.60 31/10/2018 Bidwells 200.00
TOTAL 59,366.24

I hope I have made my self clear, I look forward to receiving some help.

Regards

Stephen

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2019-08-30T00:10:44+00:00

Interesting. You were able to do it all in a Table.

I see you cleaned up his company data.

I would do 2 tweaks to your layout.

First I would put all the slicers at the top of sheet, ie move the "Fiscal year" slicer

Second, I would add a "Company" slicer.

Questions:

#1. what is the "Multi Acc Invoice"? Where did this column come from? I think I see it now, but is it really necessary?

#2. Why do some rows have various styles applied to them?

#3. Why the unreadable text color for most of the Fiscal year entries?

Stephen:

Do you need / want the amounts to be totaled?

Data entry is somewhat similar, but you need a separate row for each account type. This is a better format, actually the required format, for creating a pivot table.

D'oh! (head smack!) My mistake. I should have done this too (thanks for the reminder and inspiration Jeovany!) ...  So here is what his better formatted data looks like in a pivot

No spaces!

Notice that I put the "Designated Account" and "Fiscal Year" columns into the "Filter" area. This added the option to filter the pivot table in the upper right corner of the pivot. So you can filter by the specific account you wanted, and I picked one year, but if you need, you could also select all years.

I think this new pivot gives  you what you need.

(Yes, I am pivot crazy.)

Here is another example using the same pivot. I've filtered for "All" accounts to and one specific year, 2017. It includes a total for all accounts that year

Here is a link to my tweak of Jeovany's file

https://1drv.ms/x/s!Am8lVyUzjKfpoCf8_-gzab5ruAz0?e=xk0cgJ

Was this answer helpful?

1 person found this answer helpful.
0 comments

55 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2019-08-29T11:16:38+00:00

    Hi again

    I have been doing some more playing with my test data.

    I put ms/aldi/retail in report filter

    I then clicked on the multiple items tab

    I selected all amounts and unclicked blanks

    if I then double click the grand total at the bottom it creates a new sheet, just showing these items. 

    The problem with this is its just shows all the ms/aldi lines under each other and not sorted by company and fiscal year?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-08-29T16:20:09+00:00

    Rohn007

    Hi again 

    I noticed in your splitter file you only had the name Breheny once, however when I try to re-create it, I always get two?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-08-29T19:26:02+00:00

    Hi

    I am playing around with the pivot tables

    Row Labels Sum of M&S/Aldi/Retail Sum of Total
    June 30 2018 $2,278,809.34 $4,266,749.10
    Anglian Water $4,782.58
    Bidwells $6,583.50 $18,788.25
    BLP Insurance $5,000.00 $5,000.00
    Breheny $1,837,112.10 $3,282,004.70
    Cannon $1,698.75
    Farrell & Clark $134,110.00 $159,771.00
    Ground Control $2,950.00 $2,950.00
    HCC $27,210.16

    mine looks like

    Row Labels Sum of M&S/Aldi/Retail Sum of Total
    June 30, 2010 3254
    Bidwells 3254
    June 30, 2011 8998.03
    Bidwells 4520
    Cannon 2309.48
    Lycetts 288.75
    NHDC 1572
    Thomson Snell & Passmore 307.8
    June 30, 2012 4932.54
    Bidwells 3210.8

    As you can see yours looks better to me, I have chosen the same options but cannot see what I am doing wrong?

    Specifically what do you think "looks better"?

    Is it that I applied currency formatting to mine?  Select the cells, right click select "Format Cells", select Currency, then select one of the formats and number of decimal places. Is this a strict account report, or just a summary where pennies do not need to be counted and displayed?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-08-29T19:36:31+00:00

    Rohn007

    Hi again 

    I noticed in your splitter file you only had the name Breheny once, however when I try to re-create it, I always get two?

    That is part of the data clean up we've been telling you to do, MULTIPLE TIMES. Because the company names were typed in, several of them are inconsistent. Breheny is one that I fixed.  Some of the entries had a space(s) character at the end.  Other are mis-spelled or use "alternate" spellings, ie "Anglian" vs "Anglian Water" and "D&B" vs "D&Bradstreet" (a creative mix of abbreviation and full name ...).

    The spaces problem can be fixed using a helper column and the =Trim() or =Clean() functions

    Clean Text With TRIM and CLEAN       Run time: 2:35 ****https://exceljet.net/lessons/how-to-clean-text-with-clean-and-trim

    When you bring data into Excel you sometimes end up with extra spaces and line breaks. This short video shows you how to clean things up with TRIM and CLEAN.

    After non printing characters are fixed, the correct spelling will have to be identified and applied.  Again, that is why I suggested using data validation and using a drop down for company name entry, to prevent spelling issues.

    Was this answer helpful?

    0 comments