Decades ago I used to get frustrated with other users: Maybe automating the work isn't as fun for everyone else as it is for me, but why couldn't they see how valuable it is? Eventually I realized that if everyone were like me, no one would ever get any work done; the world would be full of people who just wanted to find slick new ways for other people to get their work done. So the world needs a few of those to make the work go faster—but only a few. Oh, this sounds like a fun project!
Ok, I understand better about the background. The main point, as I see it, is that lots of people are going to be entering the data, and you need a way of a) getting that data into a standard layout, and b) dealing with all their departures from the standard with as little manual effort as possible. You won't be able to invent any method that can deal automatically with every case ("it's impossible to write a foolproof program because fools are so ingenious"), but you want the least effort possible.
By the way, one important assumption I'm making above is that these folks will enter the data and then you'll be the one who makes sure it's ended up in the right format. You didn't quite say that, but it seems obvious because someone has to oversee the process, explain "no, not like that, like this", correct mistakes etc.
If I were doing this project I would need two things: A) I'd have to invent the best (ie easiest and least error-prone) way for the users to enter the data. And B) I must know exactly what the final format should be, whether that's imposed on me by someone else or I have to invent it. Given those two, I know I can work out how to translate from A to B.
And so can you; if you can't now, you will be able to by the time we're done. We can write either a set of Excel function or (more probably) a VBA program that will quickly and reliably transform A into B...once we know what A and B are.
And to answer your question below, yes, you can have them enter it into some other receiver (a text file, or Word, or whatever else works) and then import it into Excel. The catch is that your functions, or VBA program, have to be able to tell by looking at the text where to put each datum. That can usually be done, and sometimes without even much ingenuity.
Tell me first, then, about A: What is the (exact) format you want for the final result? An example is best, using real data in their real columns. I specify real data (not just "Name 1" / "Detail 1") because sometimes if I know what the names and details are it's obvious how to teach Excel to differentiate between them; perhaps the names are all text and the details are all numeric, or the first detail is always a month and the last one is always a dollar figure, or something. Other times it's not that easy, but that's where we have to start.
|