Summarize data automatically

[Thomas Kuriakose]

Respected Sirs,

We need to automate a quotation raw data extracted from database weekly based on date and names as follows -

1. The date field needs to be checked if it falls less than current date (Today) then the expiration status is "Expired", else "Current".
2. The summary of this data needs to be as follows -

a) Name wise number of expired quotations
b) For the current quotes, the requirement is to distribute the number of quotes name wise for the next four quarters.

The date format extracted from data base is dd.mm.yyyy. There are 52 columns in actual data and removed the excess columns to summarize the data. I have added columns C to F to calculate the date and quarter from the base data. Kindly check and advise whether the columns and formula used in C to F is correct and suggest a better formula.

The attached file has two tabs, Raw data and summary. The required output is in summary tab A20 to L32.

Kindly guide on how to arrive at this summary. The raw data will be replaced weekly in this file and the summary needs to be extracted.

Thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

I am sure others will respond ; one comment I would like to make is that when you generate a concatenated string representing a year and a quarter , or a year and a month , or a quarter and a month , or any two periods in general , try to put the larger period first , so that sorting is automatic.

When sorting , Excel will automatically place 2019-Quarter2 after 2018-Quarter4 if sorting in ascending order , whereas if the concatenation results in Quarter2-2019 and Quarter4-2018 , then the order will not be the one you expect.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you for his insight on concatenated string.

This is well noted,

Much appreciated,

with regards,
thomas

 

[p45cal]

First some simplification:

1. Delete columns C,D,E & F from your raw data.
2. Select all the data in column B then:
   1. Data|Text-To-Columns
   2. Delimited
   3. Next
   4. Uncheck all Delimiters
   5. Next
   6. Choose Column Date format to Date and DMY
   7. Finish
3. You now have true dates in column B
4. Change the header in B1 to DateOld
5. Add a new header in E1 Date.
6. Enter formula in E2:
   • =IF(B2<TODAY(),1,B2)
7. format E2 as date
8. Copy E2 down the whole list (all expired items will have date 1/1/1900)
9. Now create a pivot table using columns A:E of the list (A1:E1189)
10. Name into the row labels
11. Date (the new date) into the column labels
12. Count of materials intothe Values area.
13. Right-click the first date on the pivot (1/1/1900) and choose Group… and select both years and quarters ONLY.
14. You'll still be on a selected cell although it will now read 1900. Edit it to say Expired. (If you also try to edit Qtr1 it will, unfortunately, also edit all other instances of Qtr1)
15. Clean up the pivot: remove grand totals for rows, remove subtotals, change it to a Tabular layout, hide the +- buttons

You get something like the attached at cell A35 of the Summary sheet which is remarkably close to your required output.

 

[Thomas Kuriakose]

Respected Sir,

Thank you so much for the detailed step by step solution to this query.

Much much appreciated.

Sir, now we need to have the raw data replaced either in this file or new file and then get the summary as you have listed weekly. To automate this is a macro required to import the next week raw data to this file and generate the summary.

Kindly guide on the automation with all the columns from the raw data.

Thank you very much for your support,

with regards,
thomas

 

[p45cal]

To automate this is a macro required to import the next week raw data to this file and generate the summary.

Well, you'd better start coding then.
You can make a start by recording a macro while carrying out the process.
Attach that macro result, preferably in a workbook, here.

Kindly guide on the automation with all the columns from the raw data.

Difficult when we don't know where these columns are relative to each other.


ps.

now we need to have the raw data replaced either in this file or new file

A new file or sheet would be preferable.

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this information.

I will try to record the macro and get back to you.

Much appreciated,

with regards,
thomas