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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-08-26T16:45:27+00:00

    Hi Again

    The master sheet needs to stay as it is, for the inputter to just enter the data, so any ideas you think will achieve the end goal, I am more than willing to listen to you.

    https://1drv.ms/x/s!Aq3WqOz73fYygYUUlnjJxj5jNBE0ow?e=EVu3y0

    Copy of latest file

    Regards

    Stephen

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-08-26T16:21:45+00:00

    Hi again Stephen 

    I'm totally agree with colleague Rohn

    I've been working and helping you for a while with your file and I had in mind from the beginning the idea of overhauling your data and convert it into a table, using data validation for the company names to avoid mistakes like 

    Companies Forty Shillings and 40 Shillings in the same column in the table you posted above that will create discrepancies in your further calculations 

    To achieve the new demands you have been asked 

    You need to overhaul your data and convert it into a table to have the advantages of the built in excel features that will include similar results the macro I gave you in a previous thread

    Do let me know if you need more assistance

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-08-26T16:00:55+00:00

    Not what I am looking for.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-08-26T15:48:45+00:00

    Since you are starting with a single "input" table, you may be able to get a pivot table to do what you need.

    Have you considered using a Slicer or Filter to just show the data you want rather than actually splitting the data into separate sheets?

    ! I’ll Have a Slicer That!             2014 08 12https://www.myonlinetraininghub.com/ill-have-a-slicer-that

    Slicers were introduced in Excel 2010 and they’re an interactive control that enables you to filter data in PivotTables, PivotCharts, Excel Tables and CUBE functions. Now I know you can already filter using the PivotTable or Excel Table filter tools but Slicers are better for 2 reasons:

       - They can control the filtering of multiple PivotTables/Charts (but only one Table)

       - They look nicer and are more intuitive to use 

    ET MR PivotTables.docx

    !Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers                   2015 06 24

    Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.

    .  * Slicers – what are they? .  *  How do I Add them .  *  Can I link them to Charts .  *  Can I link them to multiple reports.  *  Are they Formattable

    .  *  So What is next

    If you insist on splitting into sheets

    Show Report Filter Pages- Create Multiple Pivot Table Reports with Show Report Filter Pageshttps://www.excelcampus.com/pivot-tables/show-report-filter-pages/****https://www.youtube.com/watch?v=sjnUThSQ8hk  (6min)

    Learn how to quickly create multiple pivot table reports with the Show Report Filter Pages feature. Download the file to follow along: https://www.excelcampus.com/pivot-tab...  Pivot tables are an amazing tool for quickly summarizing data in Excel. They save us a TON of time with our everyday work. There is one "hidden" feature of pivot tables that can save us even more time. Sometimes we need to replicate a pivot table for each unique item in a field.  

    Show Report Filter Pages in a Pivot Tablehttps://www.myexcelonline.com/blog/show-report-filter-pages-in-an-excel-pivot-table/

    When you are using an Excel Pivot Table you can show the items within the Report Filter on separate sheets inside your workbook.

    Say that you have created an awesome Pivot Table which shows total sales and number of transactions per region.

    You can drop in your Customer field in the Report Filter and replicate the Pivot Table for each of your customers in a separate Sheet.

    ET PT PivotTables Power Query BI.docx

    Was this answer helpful?

    0 comments