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-28T17:31:37+00:00

    #3

    Did you try <CTL><Z> to undo the change?

    Either don't save the current version of the file, or open an older version of the file, like my first upload and find the info there. This is test data, it has been played with so DON'T use my file for production unless you delete all of the current data and replace it with the original data, which you will then have to tweak to recreate the changes I made, like the Fiscal year text ...

    .

    #4

    OK, I recreated the blanks problem.  I'll look for a fix.

    .

    Here is one possibility (I didn't test it)

    .Hide (Blank) in Excel PivotTables        October 11, 2018    Mynda Treacy

    https://www.myonlinetraininghub.com/hide-blanks-excel-pivottables

    There are a couple of ways you can hide blanks in Excel PivotTables. To be clear, the ‘blanks’ I’m referring to are those shown below where the text, (blank), is inserted as a placeholder for empty cells in your source data:

    It’s important to point out that these (blank) placeholders only occur in row or column areas, and never in the values area of a PivotTable.

    ET PT PivotTables Power Query BI.docx

    .

    #5

    Yes, that is probably the best policy since they are so easy to regenerate, until some unknown time in the future MS fixes them and makes them linked to the origin pivot ...

    .

    If you are printing them, it would probably be a good idea to include the Printed date/time stamp in the Header or Footer. That way they are easier to relate to the master data, which may have subsequently changed.

    .

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-08-28T18:19:42+00:00

    Hi again

    I am guessing that with the original data you turned it into a table? Can you also tell me how you created the fiscal year entry?

    Regards

    Stephen

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-08-28T20:11:30+00:00

    Yes, I did convert the input data into a table. That is a requirement of importing data into the PivotTable. It is a simple process. Remove blank lines between the data, remove the unneeded heading lines, If you select all of the data, then sort it, the unwanted stuff will be grouped making it easier to delete (I didn't do that, <g> ). Then use the <CTL><T> shortcut to make it a table.

    The fiscal year text was simple brute force.  Look at the date, figure out the year end, then copy the value down until the next entry in the new year ...

    But ...

    Using a new function I just learned in the middle of last night I came up with a formula to calculate it for you (yes, I'm feeling more than a little smart at this moment <g> )

    = "June 30, " & YEAR(E2) + CHOOSE(MONTH(E2),0,0,0,0,0,0,1,1,1,1,1,1)

    The first part is just a text string

    & concatenates the first part to the second part which calculates the appropriate year.

    Year(e2) extracts the year from the date in column e (you'll have to point to the appropriate column

    then the Choose() looks at the month number from that same date.  For months Jan through June it retrieves the value 0, for months July thru Dec it retrieves value 1.  The retrieved value is added to the year extracted to give the appropriate year end

    So, delete the year end text from the data column

    enter the formula into the cell in the first row. As you enter the formula, simply click on the date cell for Excel to automagically pick up the appropriate table reference (more table automation) which is called a "structured reference". After you type the closing parenthesis and hit enter, the formula will be copied down through the whole table (as I mentioned before).

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-08-29T08:12:09+00:00

    Hi

    Thank you again, any luck with #4i still cannot get it to just show the entries for the MS/Aldi column?

    Regards

    Stephen

    Was this answer helpful?

    0 comments