Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-03-2014, 08:52 AM
pgeorgilas pgeorgilas is offline Concatenate if in descending order Windows 7 64bit Concatenate if in descending order Office 2007
Novice
Concatenate if in descending order
 
Join Date: Oct 2014
Posts: 3
pgeorgilas is on a distinguished road
Default Concatenate if in descending order

Hello,


In column A I have blank cells or numbers (could be zero as well)
In column B I have names, e.g. Peter, John, Mary etc

For example let's have 3, 0, "", 4, 4, 6, 0, 5, "", 1 in column A
and Peter, John, Mary, Kate, Alan, Nick, George, Jenny, Tony, Helen

I am looking for a way that in one cell I will get the names that have a non-zero or non-blank cell next to them BUT IN A DESCENDING ORDER separated by commas, i.e Nick, Jenny, Kate, Alan, Peter, Helen

If it is any help this is what I have been using but does not deal with the DESCENDING ORDER problem

Function ccIFblank(SrcRng As Range, ConCatrng As Range)
Dim diff As Integer
diff = (ConCatrng.Column - SrcRng.Column)
For Each c In SrcRng
If c.Value <> "" Then
ccIFblank = ccIFblank & c.Offset(, diff).Value & ", "
End If
Next
ccIFblank = Left(ccIFblank, Len(ccIFblank) - 2)
End Function

Thanks a lot
Reply With Quote
  #2  
Old 12-10-2014, 12:28 AM
excelledsoftware excelledsoftware is offline Concatenate if in descending order Windows 7 64bit Concatenate if in descending order Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

This one sounds fun. I have it figured out using an array but I need to know if you know what the maximum number in column A would be. Right now you show 6 being the highest number but I'm sure it is probably not the highest in your final workbook. If the highest number changes let me know the max it can go so I know how far to carry the array.

Thanks
Reply With Quote
  #3  
Old 12-10-2014, 01:35 AM
pgeorgilas pgeorgilas is offline Concatenate if in descending order Windows 7 64bit Concatenate if in descending order Office 2007
Novice
Concatenate if in descending order
 
Join Date: Oct 2014
Posts: 3
pgeorgilas is on a distinguished road
Default

Hello excelledsoftware,
this one has been solved. Pls check the thread under the name:
Concatenate if other column value is non-blank or non-zero BUT IN DESCENDING ORDER
http://www.excelforum.com/excel-prog...ml#post3923231
I must have created a similar thread twice and not realize.
I found it interesting too
Thanks

Last edited by pgeorgilas; 12-10-2014 at 01:43 AM. Reason: included link
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate if in descending order Concatenate function for displaying numbers in words officeboy09 Excel 13 08-09-2021 12:59 PM
Concatenate if in descending order Getting mult resps. selected in listbox to concatenate & paste at a bookmark in Word marksm33 Word VBA 3 01-15-2015 05:59 PM
Concatenate two textbox values for message box Smithy02468 Excel Programming 1 11-08-2014 10:48 PM
If, move and concatenate in VBA devcon Excel Programming 0 07-04-2011 12:44 AM
Concatenate if in descending order Concatenate/merge contents of multiple cells PSYloco Excel 2 05-14-2011 03:46 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft