Table with multiple column entries listed vertically

[GirthJohnson]

NeoPa,

Understood. Thanks for your patience. I'm obviously new to forum protocols and apologize for any confusion, diversion or offense I may have caused. I'm covering for several folks today and put forth my half hearted post as a secondary solution for sake of prosperity.

I'm clearly not a moderator or in the business of solving other people's problems and will leave it to the pros. A thousand apologies and thanks for all the help!

[NeoPa]

You're fine Girth. Sometimes I have to set the tone a little harder as we do have members that don't respond reasonably otherwise. In this case treat it simply as guidance for the future.

PS. Thank you for selecting one of my posts as Best Answer, but I had to change it for the post which had the actual suggestion in.

[GirthJohnson]

NeoPa,

You're assessment below is spot on...

"Now you want to take that multi-entry-cell and break it up so that one entry per cell:
.......Clmn A....B.......C. .......D......
Row1: (123456)(789)(4 5674)(etc...)"


However, I run into problems with this part here...

"In the cell next to the multiline (in this example case, Row 1 Column B) enter the following:
=SUBSTITUTE(A1, CHAR(10),",")
You now have a cell that looks like:
Row 1 : Column B
(123456,789,456 74,etc...)"

When I enter "=SUBSTITUTE(A1 ,CHAR(10),",") column B simply repeats whats in column A.

Also, I have another issue which may require the parsing in Access. I have over 65,000 rows and a bulk of them are formatted correctly. The rows with <alt><enter> carriage returns are spread through out the worksheet in no discernible pattern and the number of <alt><enter> vary.

[zmbd]

Girth,
We may need to split that into a new thread.
Please look very carefully at my post #10
You'll see that there are commas there... they are in red.
I'll go back into the post and try to make that more obvious.

[NeoPa]

Girth, I'm now quite confused. Firstly, I believe you're referring to Z's post #10 rather than anything from me. That's straightforward enough, but are you now saying that my assessment of your needs as posted in #11 is wrong? I believed you had confirmed that it was correct. If it is not then I, for one, am confused. Can you say if, when you see multiple lines of Account Numbers for a particular item, they are in multiple lines within a single cell, or alternatively, they are in multiple cells below each other which would be indicated by your initial comment "The last column is laced with multiple entries per record listed vertically which creates mostly empty rows with one cell of data."?

These two interpretations are mutually incompatible and, to be able to help, we need to understand which of the two you are saying.

[GirthJohnson]

Z / NeoPa,

I apologize for confusing everybody and thank you for your help.

I went ahead and parsed the issue out in Access with a bit of VBA. The excel option was probably simpler but I couldn't make it work. I think there might have been a file conversion issue at play as I'm still working with 2003 and the original file was prepared in 2007.

Again, thank you for taking the time to help me out. I really appreciate it!

[NeoPa]

That's reasonable Girth, but an answer to my question (Post #20) to clarify what the actual original question is really about would be of assistance to any that read up on the thread, and of interest to all those who are already involved, if you wouldn't mind.

[GirthJohnson]

NeoPa,

Again, I apologize for the confusion. I was actually responding to Z's post. I cut and pasted from his post (#10) and accidentally addressed it to your name in my post (#18).

[zmbd]

The solution given in post#10 will solve all the issues as given in OP and in #18.
IF there are no CHAR(10) in the cell then the formula simply returns the original cell contents.
The formulat does not care about the number of CHAR(10) it replaces them all.
The cut-paste-special-values is the magic
The text to columns is the trick
Now it won't work to get those cells to columns that had single entries between the customer entries; however, you have a solution to that already. :)

And yes... sometimes opening a 2007 file in 2003 can do wierd things; however, I have an old install at work that I tried this on after seeing your post... worked without fail... then ofcourse, I'm using 2010 - my company skipped the 2007 just like they skipped the WinME and Vista.... and looks like Win8 now too :)

[NeoPa]

Z:
I'm using 2010 - my company skipped the 2007 just like they skipped the WinME and Vista.... and looks like Win8 now too :)

That's exactly what I did for my company (and me of course). I'm talking of where I worked prior to going freelance of course. I haven't made any hard decisioons on Win8 yet, but I wouldn't be surprised if that turned out to be another raspberry (Just like all the ones you itemised).