Forum Discussion
Formulas for Multiple Conditions and Formatting
I have a very large amount of data (30,000 lines) in Excel that contains multiple extra spaces between lines and was created with reference fields on both the top and left side. There also isn't the same amount of lines/spaces between item numbers. I've tried many formulas to pull the information apart so that I may be able to use a Pivot Table or other similar method. Any assistance would be greatly appreciated. Below is an example:
3 Replies
That's a pretty classic case of a bad design (at least for Excel). I'm going to assume you aren't the person that created it. The question, then, is this: what IS the source of all that?
- Is it generated regularly and passed to you?
- Is it possible to receive the "raw" information in some kind of different format?
In short, if I were in your place, rather than trying to make this work--even if it is possible to do so without compromising data integrity--I'd want to go back to an earlier stage (if that's possible) and fix the problem at that earlier stage.
- mathetes
Thank you very much for your feedback. Unfortunately, this is the only data provided to me. I requested a more "raw" form of the data; but I'm currently on the IT department's backlog. I was looking for a way to simplify this information in order to proceed with my assigned task while I await IT.sunderdown That would be something for PowerQuery. Can you share a sample of the data as you receive it an explain how to interpret it? The screen shot doesn't clarify much. At least, not for me.
Use Onedrive, Google docs, Dropbox or something similar to share a file.
