Hello Excel Fundis
I have a raw data file that I need to format every month for reporting. The raw data format makes it difficult to work with so I am looking for a quick and simple way to make the content usable. I had a similar query recently and a simple pivot table worked, but this is a bit more complex.
Purfleet, you helped me with something similar in January -- are you out there?
Attached is an extract of the file with problem cells colour-coded as follows:
* orange and yellow rows to be deleted
* data in columns A, B and C to be copied down from row above where shown in blue
The file also shows what the report would look like once formatted.
Any ideas are welcome!
Thanks CINDY
Hi Cindy,
Where does the raw data come from? You say a file, so it's not in Excel to start with? What generates the file?
Is it always in this format i.e. same number of rows, same blank rows, same duplicate account numbers?
Regards
Phil
Hi Philip, yes it does come in an Excel file but it is so badly formatted that it is almost unusable and it takes me hours to get it into some sort of shape for the reports I have to do. It is sent from the my company's BI team who handles all the data processing. Up until now I was quite successfully using a simple pivot table to format the data the way I wanted it, but recently the format has changed and the file is full of blanks that are causing a problem (I have tried deleting the rows using the Go to Special/Blanks function but I cannot get it to work). The data itself -- what I have called Account Number, Customer Name, Product Code etc in my mock-up -- will vary every month. Any ideas? thanks CINDY
Hi Cindy,
Sounds like your BI dept are causing you a lot of headaches 🙁
Sorry to ask more questions but the key here is to get raw data in a standard format, otherwise any VBA solution I write will have to be changed the next time you get data that is in a different layout.
For example, I can see that :
- any blank rows can be deleted
- any row that has the same account number as a row above it can be deleted
- any cell that is empty, but where other cells in the row are not empty, I can fill down the value from above it.
If any of these things change - because the layout or content of the data changes - any VBA I write now won't work on the next bunch of data.
If the data will always be in the same as I've described above, I can write VBA that should always work for you. Will it always be the same?
Your BI people should be supplying it to you in the same layout!
Regards
Phil
Sorry i did this and forgot to reply, and i have just assumed what Phil has asked
Anyway its a bit quick and dirty,b ut this might work
Purfleet
Hi Purfleet, not sure I'm seeing what you want me to see! did you do something magical on the spreadsheet to format it the way I need it? Phil's questions are very valid -- the data will vary every month in all columns and rows. I just need a tool to get rid of the blanks (when I do it the standard way it messes up the data) and drag down the content where I need it. I do not want to attach the actual file here (it has valid customer info in it), I have just done a mock-up, but am happy to email it if you tell me where. tks CINDY
