Merged Cells 1

[SMosley804]

Example:

I have data that is being exported from a requiremnts management system into excel. the problem that I am up against is as follows

column1 column2 column3
Req ID Author Requirement


row 1 FR123 JUser There are 3 choices
row 2 Choice 1
row 3 Choice 2
row 4 Choice 3


What I need to have happen is that Choice 1, 2, and 3 should appear on row 1 in the same column because those options are apart of that requirement row. But the export from another system inserts the choices on its own row rather that inserting it in the cell to which it truly belongs. Is there a way that I can resolve this problem through use of either a macro or vb script? please advise.

 

[SkipVought]

hi,

What is the purpose of this data?

How will these 3 choices be used?

is this the ONLY DATA in this sheet? If not, what is it?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SMosley804]

The data is software requirements. The three choices are merely a part of the requirement. that should be displayed in the same cell for row 1 and column 3. Yes this is the only data in the sheet. There are additional informational columns but this is the jist of it. The additional columns are not really relevant to solving this issue. Are you able to help?

 

[SkipVought]

You have not really explained WHY these 3 values need to be in one cell.

How are you going to USE this data in your workbook?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SMosley804]

The three values need to be in the one cell because it is a part of the requirement. 1 requirement should not be split accross multiple rows. Are you able to recommend a solution for the issue? Is something that can be accomodated in excel via VB code or macro or not?

 

[SMosley804]

Also if you read the original post it clearly states

"What I need to have happen is that Choice 1, 2, and 3 should appear on row 1 in the same column because those options are apart of that requirement row."

I also answered the question in my reply to your initial reply

"The data is software requirements. The three choices are merely a part of the requirement. that should be displayed in the same cell for row 1 and column 3."

Don't want to offend you but if you don't really have any suggestions for resolution it probably be best that you not continue to ask the same question that has been answered multiple times.

 

[Bong]

Sub combine()
    Set r = Sheet1.UsedRange
    For Each c In r.Columns(1).Cells
        i = c.Row
        If c.Value <> "" Then
            i2 = i
        Else
            Sheet1.Cells(i2, 3).Value = Sheet1.Cells(i2, 3).Value & vbLf & Sheet1.Cells(i, 3).Value
            Sheet1.Cells(i, 3).Clear
        End If
    Next
End Sub

_________________
Bob Rashkin

 

[SMosley804]

Thanks Bong that worked like a charm. do you know if there is a function that I can use to remove the blank rows that are left behind.

 

[SkipVought]

SMosley804,

Over the past 4 years you have posted many questions and received many helpful replies from Tek-Tip members. Yet, you have NEVER posted a customary "THANK YOU" for these valuable posts.

Since the solution posted by "Bong that worked like a charm," it would be most fitting to...

[blue]
Thank Bong
for this valuable post![/blue]

These [purple]Little Purple Stars[/purple] identify threads for Tek-Tips browsers, as ones that have been helpful. They also act as a token "Thank You" to the one or ones who posted valuable information. And they identify the recipient as a grateful member.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bong]

Simpler would be to sort the data and thereby move the filled rows together:
r.Sort Key1:=Sheet1.Columns("a"), Header:=xlYes

_________________
Bob Rashkin

 

[SMosley804]

Dear SkipVought,

I'll just assume that you don't really know how to read or comprehend words on a page. If you did, you would recognize that I did thank Bong for his post. Furthermore, what business is it of yours. You seem to be all in everyone's post without ever offering anything of any value except a host of irrevelant questions. Get a CLUE; Please and Thank you.

ps. this will be the last time I will respond to your ignorance, since I really do question your ability to read for understanding. Maybe you should take a 1st grade reading class or gain some self esteem. you Cyber bully! Bye Bye!

 

[SMosley804]

and to Bong,

Thank you again for your help in this matter. I really appreciate when a person offers a possible solution. In this case it was exactly what I needed.

 

[stduc]

SMosley804 I believe what SkipVought meant was for you to give Bong a star, in addition to thanking him within the tread. It is customary here to click on the "Thank" link below a post if you feel it is helpful. You will need popups enabled.

 

[Gavona]

Like Skip I struggled to understand SMosley804's requirement.

As for the comment about Skip never offering anything of value, on this forum he has been awarded 1463 of those purple stars for "valuable posts". This is not quite as many as he has been awarded on the MS Office forum but still makes him the most valuable contributor to both Fora.

Gavin

 

[SkipVought]

SMosley804,

I may get a bit pedantic from time to time, but it is because I have observed, over the years, that many spreadsheet users are ignorant of many Excel features. I include myself in that number, and I am still learning. Consequently, I probe to determine what the user is attempting to accomplish, and I occasionally do not simply suggest a solution initially. I am leaning to be a better wielder of my Excel toolbox and I hope to lead other toward that goal, as well.

In your instance, you have 3 values in 3 separate rows. If your aim, were simply a visual or display issue, then there would be no reason to concatenate the 3 values: there are ways to make what you seem to have wanted, appear so. Otherwise, if you had a need to use the 3 concatenated values, to COPY/PASTE or for some other range or application to reference, then the concatenation makes perfect sense.

However, if neither is the case, it does riddle me, Batman.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}