Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List cell values seperated by comma if criteria met
I created a sheet as below:
A B 1 Company Name When Approved 2 ABC Company 6/1/07 3 XyZ Inc 5/1/07 4 aaa LLC 6/2/07 5 aba Company 4/20/07 There's a lot of other info but these are the columns i want to use for this formula. Anyway what I want to happen is if month(A2:A5) = month(today()) then I want it to list the name of those companies on sheet2 a1 with each name seperated by a comma. Do I need to make a macro to do that or is there a formula that can do this? Even if it has to list them down a column that would be fine as long as there are no gaps in between. I figured out a way to do it but it just leaves a blank cell if false, so it may have one company name and then 20 blank rows and then another company name??? Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List cell values seperated by comma if criteria met
2 solutions at your other post
Biff "Mike Pearson" wrote in message ... I created a sheet as below: A B 1 Company Name When Approved 2 ABC Company 6/1/07 3 XyZ Inc 5/1/07 4 aaa LLC 6/2/07 5 aba Company 4/20/07 There's a lot of other info but these are the columns i want to use for this formula. Anyway what I want to happen is if month(A2:A5) = month(today()) then I want it to list the name of those companies on sheet2 a1 with each name seperated by a comma. Do I need to make a macro to do that or is there a formula that can do this? Even if it has to list them down a column that would be fine as long as there are no gaps in between. I figured out a way to do it but it just leaves a blank cell if false, so it may have one company name and then 20 blank rows and then another company name??? Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List cell values seperated by comma if criteria met
How about:
Sub mike() Dim d As Date Dim s As String s = "" m = Month(Now()) n = Cells(Rows.Count, "B").End(xlUp).Row first = True For i = 2 To n m1 = Month(Cells(i, "B").Value) If m1 = m Then If first Then first = False s = Cells(i, "A").Value Else s = s & "," & Cells(i, "A").Value End If End If Next Sheets("Sheet2").Range("A1").Value = s End Sub -- Gary''s Student - gsnu200726 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exctracting comma seperated data from a single cell | Excel Discussion (Misc queries) | |||
Find the sum of Comma Seperated Values In a Cell | Excel Worksheet Functions | |||
Sum of comma seperated values in cell(Reposting) | Excel Worksheet Functions | |||
Put all cells in one cell seperated by comma | New Users to Excel | |||
column values to a cell with comma seperated | Excel Worksheet Functions |