conditional text merge

I want to merge text under the following criteria,

First issue (data in Col A in sequence)
Col A(text)   Col B(text)
a   gg
a   hh
a   s
b   r
b   ee
c    tt
d    ss
d   ee

Merge to:
a    gg hh s
b    r
c    tt
d    ss ee

Second issue (data in Col A not in sequence)

a   gg
b   r
a   hh
a   s
b   ee
d    ss
c    tt
d   ee

Merge to:
a    gg hh s
b    r
c    tt
d    ss ee

Is it possible to merge it?

TIA

norik

--
Message posted from http://www.ExcelForum.com

0
6/11/2004 8:29:56 AM
excel 39879 articles. 2 followers. Follow

2 Replies
356 Views

Similar Articles

[PageSpeed] 4

Hi
yes it is possible but will require some VBA. below one 
way:
1. Create a unique list for column A on a separate sheet:
- select column A
- goto 'Data - Filter - Advanced Filter' and check 'Unique 
records'. Copy this list to a separate sheet. 
- assumption: you have this unique list of columnA values 
in sheet 2 in column A

2. Download some add-ins to help you combine the data:
- download Alan Beban's array functions:
http://home.pacbell.net/beban
- download the free add-in Morefunc.xll
http://longre.free.fr/english/


3. Now enter the following formula in sheet2, in cell B1:
=MCONCAT(VLOOKUPS(A1,'sheet1'!$A$1:$B$100,2),", ")
and copy this down

>-----Originalnachricht-----
>I want to merge text under the following criteria,
>
>First issue (data in Col A in sequence)
>Col A(text)   Col B(text)
>a   gg
>a   hh
>a   s
>b   r
>b   ee
>c    tt
>d    ss
>d   ee
>
>Merge to:
>a    gg hh s
>b    r
>c    tt
>d    ss ee
>
>Second issue (data in Col A not in sequence)
>
>a   gg
>b   r
>a   hh
>a   s
>b   ee
>d    ss
>c    tt
>d   ee
>
>Merge to:
>a    gg hh s
>b    r
>c    tt
>d    ss ee
>
>Is it possible to merge it?
>
>TIA
>
>norika
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
frank.kabel (11126)
6/11/2004 9:55:00 AM
Frank,

Thank you for your help.

norik

--
Message posted from http://www.ExcelForum.com

0
6/11/2004 11:45:47 AM
Reply:

Similar Artilces:

Macro to delete and replace a text box
I use a macro in a master file to populate sub-files with data. I would like to delete an existing text box in the sub-files and replace it with a text box from the master file. Is there a way to do this using a macro? Any information is appreciated. Thanks ...

Mail Merge and Access format of telephone number
I use mail merge for a directory using Access for my data. The telephone numbers that are formatted in Access (555) 642-5555 aren't retained but come in to merge as 555642555 so I have to go to each one and put in the (xxx) 666-666. Is there anything I can do to retain the Access format of the tele numbers??? -- Terry50 See http://www.gmayor.com/formatting_word_fields.htm. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "terry50" <terry50@discussions.microsoft.com> wrote in message news:12784D...

How to delete text when it only appears in print review Publisher
I have a hidden x that can't be seen to delete it in publisher. It is visable in print review and prints out on paper How to find and remove it Maybe it is on the background. In Publisher 2000 and below, view, background, newer Publisher, view Master page. Do you have the picture detail selected? View, pictures, detailed display. Try selecting sections of the publication, it might become active. Is it a crop mark? If all fails, send the file to me, I'd like to see what is going on... Remove "my" to reply. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvp...

quoting selected text in e-mail reply
I have Outlook for Office XP, and I would like to be able to reply to e-mails by quoting selected areas of text from the original message. I can "quote" the entire message and delete certain parts, but I have not been able to select a certain part of the text and reply with just that part quoted. Thanks ...

Conditional formating #4
I want a macro that will hide the values in a cell if another cell is blank Name Cost Paid Balance Jane Doe $10.00 $10.00 John Doe 5.00 5.00 5.00 Using Excel 2000 If Name is Null or Blank Hide Balance. If Name is not null show balance. Tonie Torie, Use Format>Conditional Formatting and set the dont colour to white. The formula would be =$A1="" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tonie Crawford" &l...

merging values to word
i am merging numbers to word documents and the formatting will not stay. e.g. 45698723.2365879 is what shows up in my merge, however in excel i have formatted it so it appears as $45,698,723.24 can anyone help on this issue? ...

Function In A Text Box
Hi, I am trying to put a concatenate function in a text box. The error message is as follows: The text you have entered is not a valid reference or defined name. I want to concatenate some text with a number from the cell, A2. Any ideas would greatly appreciated. Regards Tubbsy -- tubbsy ------------------------------------------------------------------------ tubbsy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24743 View this thread: http://www.excelforum.com/showthread.php?threadid=388332 Its not a perfect solution but you could perform the concat...

How to Synchronize Combo Box with Text Box After Going to Next Rec
I have a combo box called [Title Pick] that I use to display catalog titles to get to the record for that title. I also have a text box, called Title, that shows the title I just selected in the combo box. In addition, I have several navigation buttons used to go to the next record, previous record, etc. After I navigate to a different record using one of these buttons, the text box, Title, has the correct value -- but the Combo Box, [Title Pick], still shows the value from the last time that combo box was used. How do I now get the combo box to show the correct title for that reco...

Need Help with Making Address Labels with Data Merge
Hi Anyone, I have done this before, but I downloaded the latest update to Office 2004. I have created my excel spreadsheets and cannot make data merge help me with address labels. I would be better off hand writing over 250 addresses on mailers as I have put in over 10 hours of time and am able to make address labels but 24 pages of labels only produces 8 addresses over and over again. Anyone with tips out there? Feel free to email me at melanie.m.hahn@gmail.com. The entire process has aggrevated my back and so I will now go lie down and perhaps some miracle will take place. Thank you f...

Outlook (and Word) Spell check original text in reply
I have a couple of my users where Word spell checks original text i reply even though it is marked in tools as Do not Spell Check Origina Text in Reply. If I turn Word off as the editor, then it works fine. This does not occur on all of my machines, only on two... I use Word 2 and Outlook 2K. Any ideas why this is happening? I appreciate the help -- rcstechnologyPosted via http://www.officehelp.in - &lt;a href=&quot;http://www.officehelp.in&quot;&gt;Microsoft Office Software&lt;/a&gt it's because they have a prefix character set. http://www.outlook-tips.net/...

Font Color Conditional Formatting/Color Scaling
tWithin a matrix of data, I want to use two conditional formating/color scaling: one for the background color of the cell, the second for the font color corresponding to a different (but same size) data matrix. I know how to use the conditional formatting/color scaling for the background color--however would it be possible to have a different color scaling for the font color (and applied to one data matrix but referencing a separate data matrix)? Or even, can I at least use the conditional formatting/color scaling but changing the font color instead of the background cell col...

Word Merge access hyperlink fields.
This is for Albert Kallal or anyone familiar with his Easy Word Merge file. I am a new user to access and have created my first database. I have downloaded and am using Albert Kallal's single word merge. I am having a problem now with hyperlink merge fields as they are displayed as an address and not a hyperlink . I would also like to know how I can merge more than 1 record to a single word document. I have based my form on a query with multiple tables, but when the form is merged only the first record in the query is displayed! Many Thanks. -- Lyndy ...

text in downward arc?
Hi there, using publisher i want to create text in a downward arc. i have tried to use the upward arc in the word art section and invert that but the text flips. i want to be able to read it from left to right. No matter how i try the text will not read from left to right? what am i doing wrong? Word art will do that. Which version of Publisher are you using? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "ian cobbledick" <ian cobbledick@discussions.microsoft.com> wrote in message news:64D9450C-7416-453...

search for text then return text
i need a fomula that will search down a specified cells in a column... look at the words in each cell and then display the text i want. for instance... I have a column with words in it and i want it to search through each cell/word for the letter "P"... then i want it to display a "P". if there are one or many instances where a "P" is in that column I want to only display "P". Maybe something like this... =IF(SUMPRODUCT(--(ISNUMBER(SEARCH("P",A1:A20)))),"P","") In this formula case is not a factor. P an...

How do I create a bulk mailing text box in Publisher?
-- Thomas -- Mike Thomas wrote: ...

conditional formatting
I have used conditional formatting to highlight some issues in a spreadsheet. However, to distribute this, I need to remove the link to the base data used as the source file is too big. Is there a way I can copy the cell shading so that the condition is removed but the formatting remains? I am using 2003, and the spreadsheet is 7500 rows x 70 columns and running on a laptop, so any solution can't be too memory or processor intensive! Thanks for your suggestions Take a look at http://xldynamic.com/source/xld.CFConditions.html and see if it helps, although it is not si...

Button text #3
I know I can change the text on a button with the following: GetDlgItem(IDC_BUTTON1)->SetWindowText("Enable"); What I would like to know, is it possible to get the text from a button? In other words if by clicking a button I change the text on the button to "Disable" but when clicked again it says "Enable". Can I check to see which it currently is? Thanks GetWindowText(...) but instead I would keep track of that with a boolean variable. m_Button1.SetWindowText(m_ShouldSayEnabled ? "Enable" : "Disable"); AliR. "Ed" <e...

Merging data from two worksheets? #3
BenjieLop Wrote: > Assuming that your worksheet 2 columns are arranged this way: > > ColumnA ColumnB > StaffID Ethnic Origin > > Your formula is: > > =vlookup("CellNumber",Worksheet2!$A$1:$B$100,2,0) > > where "CellNumber" is the cell address that has the specific *Staff ID > that you want looked up for the corresponding *Ethic Origin* > > AND *$A$1:$B$100 * is the assumed range of your data in Worksheet 2 > You can modify this range according to your actual tabl > configuration. > > Hope this will help y...

Meeting requests appearing as text
Hi All, I've googled this problem a lot and not found anything useful. We have a client workstation running XP and Outlook 2003 that finds when they send meeting requests it just comes through as text, not a normal meeting request where you can click accept decline etc. I have sent test messages from his machine to myself (external) and I am also running XP with Outlook 2003. I have found a workaround - when setting up the meeting, go to actions -> forward as ical. But the user shouldnt have to do this should they. Any suggestions? Cheers Chris Hi have a look on this site: F...

Difference between .text and .value property
Hi recently I was trying to create a userform where I was giving the valu of text box to a cell. The textbox value was more that 255 chars and used following method. range("A25").value = txtUpdate.value which then I tested I got a result as #Value error in the cell... I changed the code to range("A25").value = txtUpdate.text and it worked.... can somebody tell me the difference between the above 2.. Thanx in advance Keda -- Message posted from http://www.ExcelForum.com ...

Text Formatting for URL
In CRM, On the accounts form, we would like to have an field point to a portal site where we would keep more information about a client, I have made the field a URL formatting type however it will not show up as a URL just as text. Any help would be greatly appreciated. Stephen Hi, you cannot add a field that is a link. Can you add a button at the top of the window as an alternative? <scrocker@hanebutt.com> wrote in message news:1102372455.314206.323180@c13g2000cwb.googlegroups.com... > In CRM, On the accounts form, we would like to have an field point to a > portal site...

How to keep textbox from covering text when reducing?
When I try to reduce a title textbox that came in a template, it covers up part of the text that is in it. I don't think the text is a separate object from the textbox and I have worked with the formatting over and over and nothing works. Please help me. What template in what version of Publisher? Is the text box part of a group? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "lhag" <lhag@discussions.microsoft.com> wrote in message news:57C17F65-7CC7-4C88-9B27-A1942B38B224@microsoft.com... > When I try to...

"Outlook Today" button text? And "alt-k" for OLToday?
Office Outlook 2007. Having installed OL2007, I had an "Outlook Today" button available, which had both text and icon on it. Also, clicking "alt-k" while on any page other than OL today brought me there. This morning, I uninstalled Business Contact Manager, because I have no use for it. Having done that, my Outlook Today button vanished. I was able to get it back, but it has only the icon, with no text. That's really not a big deal, except that the button is smaller than I'm used to, and thus not as easy to spot. I'd like the text back with it. Also, al...

Do I have to use Conditional Formatting or a Macro?
Hi All, I have a table like this: Value1 Value2 Value3 aq x 3 aa y 5 The cells under 'Value2' already have a ConditionalFormatting (with 3 conditions) to change the background color. I'd like to know if it's possible to apply a format (ie just border) to the entire row, and copy the ConditionalFormatting regarding the 'Value2' column, as soon as I insert a new record in 'Value1'. That is, when cells under 'Value' are not blank, then the row will have a border, and cell under 'Value2' has ConditionalFormatting...

Conditional Formatting in Excel
Hi, If D1 is empty then G1 should be white(clear) if D1 has a number in it then G1 should be red additionally; if a value, either a figure 0 or a currency value is entered in G1, which owing to the previous two conditions, would be red, then G1 should change to blue. If anyone can explain how to acheive this I would be extremely grateful. Thank You Brian Ok, you have a few conditions: Isblank(d1) means clear Formatted as Currency Means “Blue” Any value not formatted as Currency Means Red Step 1 I would insert a column to calculate this and format based on the resu...