Formula Automation

[haube103]

Hello Excel Forum,

I'm a new member and I have a question that I'm not sure how to phrase exactly. I'll post what I want but this will be done manually, but I'd really like it if there was a formula that could do it for me.

A A
A A
A A
A A
B A
B
B B
C B
C B
S B
S
S C
S C
S C




The Left side is the first column, and the right side is the second column. Please Note I've placed S's in a white font color at the bottom of the first column, they are just place holders so that the B's and C's in the second column don't move after this has been posted.

I need a formula that will be able to take the values from the first column, put them in the second column with an extra of each of the letters and a blank cell between the different sets. Is this at all possible through the use of a formula? I've been trying with various if Statements, but so far I've been unsuccessful.

I'd appreciate any answers, and especially the one that solves this conundrum. Thank you all.

Please let me know if I've done anything wrong per formula etiquette or if I need to add any clarification to my post. Thanks again. I really appreciate it.

[LewisJ]

In B2 put"

=A2&" "&A2 and drag down the column B

[BadlySpelledBuoy]

Does this need to be done with a formula?
A VBA solution may be easier.

BSB

[haube103]

Hello LewisJ. Thank you for replying. Unfortunately, this does not help me. That's okay though. I should have made myself more clear as to what I wanted. I'll attach a sheet to the original post and you'll be able to better see what I mean.

[haube103]

Hello BadlySpelledBuoy,

I'm not opposed to a VBA, but I have zero experience with a VBA and so I wouldn't know how to even use it.

[haube103]

Here is a sample of what I'd like to do. I'd really appreciate a formula solution.

[BadlySpelledBuoy]

I cannot get my head around a formula only approach to this right now, but I will keep thinking on it.

In the mean time, attached is a sample workbook with a VBA solution. Just click the button on sheet one to run the macro.

I'm sure it's not the most efficient (I threw it together just to get you going) but it works as required.

Code copied below:

Please Login or Register  to view this content.

BSB

[haube103]

Thank you so much BSB. I really appreciate your help.

This is definitely pushing me in the right directions. But you see, as you might have guessed I'm not really going to be using A, B, & C. I'll be using other phrases, and I've seen that I can put whatever phrase I want in it, and It'll do the same thing.

My next question is though. I'm trying to create this sheet in such a way that If I were to ever add anymore data or take away any data from the first column then the sheet will modify itself, or with the click of a button. But the point, is I'd like it to be flexible when it comes to the addition or subtraction of data. Can you please help with that?

If I need to clarify myself more, let me know and I'll do my best to rephrase what I've said.

[BadlySpelledBuoy]

I'm not sure I understand. If you amend the data in column A, by adding/amending/deleting, then run the macro again it will adjust column B appropriately.

As I don't fully understand why you need to create this in the first place it's difficult to pre-empt what would be an ideal solution for you.

BSB

[LewisJ]

This does seem to be pointless but anyway I am getting out.

[haube103]

It's for work. And I just checked it does Accommodate what I wanted.

What I did notice about the macro you sent me is that it doesn't add the extra "C". It Adds the extra B & A but not the C. How would I fix that?

Thank you for your help.

[haube103]

This does seem to be pointless but anyway I am getting out.

It's not pointless to me. But thank you for trying to help me. It is appreciated.

[BadlySpelledBuoy]

What I did notice about the macro you sent me is that it doesn't add the extra "C"..

It adds the extra C for me. We start off with 2 and end up with 3.

That's what this line does:

Please Login or Register  to view this content.

BSB

[haube103]

It adds the extra C for me. We start off with 2 and end up with 3.

That's what this line does:

Please Login or Register  to view this content.

BSB

You're right. That does happen; however, when I added another C to the first column it doesn't add the extra C. Does adding an extra C below the first two c's result in you getting four C's in the second column. when you press the button.