Hello Everyone,
I have come across a situation where I have to collate data in a particular fashion. I'm attaching an excel file containing both the input and the output I'm looking at. It contains two problems, would appreciate if anyone can help me solve this riddle.
See if this helps...
http://www.excelforum.com/excel-gene...ngle-cell.html
HTH
Regards, Jeff
Thanks for the quick response
Problem 1: It is providing the desired result, but can we have any formula that do the same thing?
Problem2: This remains unanswered.
For Problem2, the same thread will solve it, but for a formula in E2 copied down...
=A2&";"&B2&";"&C2&";"&D2
For Problem1, if you want it to remain dynamic, then instead of the UDF try this and just attach it to a command button to run
Please Login or Register to view this content.
How will I get the colors associated with each column i.e. Pink for Col A?
Please check the output column of problem2...Originally Posted by Chatbot
That will require VBA, but you can't mix with a worksheet formula.
Do you want VBA?
It would be gr8 if you can explain the code.
You could use this:
E..g., in B2,Please Login or Register to view this content.
=CatR(A2:A65536, ";")
Last edited by shg; 08-26-2011 at 03:54 PM. Reason: replace chopped line
Entia non sunt multiplicanda sine necessitate
Waitin for the vba code
Its looks incomplete, plz attach the excel file
That is the complete VBA code.
Put the code in a code module, and put the formula in the cell as in the example.
Adding a Macro to a Code Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel
Sorry, a line was chopped off (thanks, Jeffrey), fixed in prior post.
Else
For Each vItem In Intersect(rInp, rInp.Worksheet.UsedRange)
sItem = CStr(vItem)
If Len(sItem) Then CatR = CatR & sItem & sSep
Next vItem
End If
If Len(Ca
Please check the closing, something is missing I guess.
is this for problem 2? Its not working.
Prob 2: I have four columns which contains data, each column has a different color.
please check the output
Problem 1.
Help me collate data in the attached fashion.
If somone can solve the problem 2
For problem 2 you can not use a udf but instead you will need to make use of the worksheet change event.
right click sheet tab and View Code.
Paste the following.
Please Login or Register to view this content.
Please explain this code with the help of an example, I'm unable to execute it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks