I have a bunch of cells (could be varying amounts from 2 to about a 100). I need a macro which can find out how many rows in column A contain values and then i need to pick values from each cell in column A and put them in Cell B2 seperated by a comma.
eg.
Column A Column B
22 22,35,67,34,56
35
67
34
56
Column A could contain as many as 100 values.
I'm have a list of keywords from google adwords:
cabinet hardware
cabinet hardware knobs
cabinet hardware pulls
kitchen cabinet hardware
amerock cabinet hardware
antique cabinet hardware
These are all in A1, B1, C1, D1, E1, F1
I need to have them look like this: A1 cabinet hardware, cabinet hardware knobs, cabinet hardware pulls, kitchen cabinet hardware, amerock cabinet hardware, antique cabinet hardware. How do I do it? Im a COMPLETE Newbie when it comes to excell but I urgently need to figure this one out....how do I do it?
How would I concatenate a large range of cells, most of which are blank, and add a comma between each cell's value? My range is E2:Y2 (I will be filling down), but most of the cells are blank. For example, if I F2=9, I2=11 and X=25, I would like to display them as: 9,11,25. If in the next row, G=10, K=15 and Y=27, they should look like this: 10,15,27.
View 3 Replies View RelatedI want to be able to look up values based on what I put in a seperate cell. For instance, I want to be able to type in 11 in cell B1 and have A1 return the value for E11. Then if I change the value to 10 in B1, the value for E10 would be returned.
View 3 Replies View RelatedI need to be able to select up to 5 different models in cell A1 from a drop down list then have cell B1 look these values up and find the volumes and then return the sum of the volumes - Cell B1 should read 130. There will always be one model selected but it will vary from 1 to 5 selections.
M1,M3,M4,M5
=10+30+40+50
M1
10
[Code]....
I have a single cell with 4 values in it all separated by commas and i would like to pull out each one separately. The number of characters in each value changes.
View 9 Replies View RelatedIf I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
I am trying to merge three cells using the following formulae =CONCATENATE(A3,": ",B3,", ",C$1,", ",C3,"") where C$1 is a title (header) I will use if C3 is populated. However, I would like to leave C$1 out if C3 is not populated.
I need to concatenate all cells filled out in a column (for example A in sheet1) in a cell in sheet2. More exactly.
column A (Sheet1) (Sheet2)
aaaa cell(2,2)=aaaabbbbcccc
bbbb
cccc
If I use a VBA macro I can write this VBA code
RowsNumber = Application.CountA(Sheet1.Range("A:A"))
Then loop from 1 to RowsNumber and concatenate values in cell(2,2) but I need, if this is possible, to define a FUNCTION (nested functions) in cell(2,2).
is there a way that i can insert cell values in a userform
i have data which needs to be displayed on 33 lines
line 1 cell value from d10 then space cell value from d185 then space cell value from d186
this needs to be repeated
line 2 cell value from e10 then space cell value from e185 then space cell value from e186
line 3 cell value from f10 then space cell value from f185 then space cell value from f186
line 4 cell value from g10 then space cell value from g185 then space cell value from g186
this continues trough to line 33
Attached file where i m not able to split data in seperate seperate coloumn
View 3 Replies View RelatedI have a spreadsheet which has in one column a list of offices in Column H. In Column K there is a lst of locations. In these cells the 'location' has a value of for example ( abcdb 1234), however in a cell there may be more than one value eg (abcd 1234, xxxxx 3241,). I need a macro or formula to count the number of locations per office and also a macro or formula to count the number of unique locations per office and the number of unique locations overall. A small example of the spreadsheet. This spreadsheet can have thousands of rows.
Column H Column K Office 1 abcs 1234 office 2 locationa 2435, locationb 6521 Office 3 abcs 1234 Office 4 locationa 2435, locationb 6522 Office 5 abcs 1236 Office 1 locationa 2435, locationb 6522
I have a list of emails I need to merge into one cell and separate by commas. It looks like the follwing
A
1. xxx@xx.com
2. xxxx@x.com
3. x@x6.com
I need it to look like
A
1. xxx@xx.com, xxxx@x.com, x@x6.com
I have a file with list of names in the attched file.They are not in the same format coming from the source in my company. I need only the names in the separete column without the dates and the commas. The text is not in the same format. Looking at the data, can someone help me make a formula to separate only the names from the the entry.
the data is huge and it takes lots of hours to clean this data.
I am attachign the excel file for reference.
I have a column of contacts. Each contact has data spread out over many rows, most of which are blank. I would like all of the data spread out through the rows to be in one cell, delineated by commas (so I can import into another program).
Concatenating the cells works except that I end up with 20 commas for two strings of info.
Just so you understand I may have this:
Mary red blue yellow purple
Rob blue purple
Trey yellow
and I want it to look like this
Mary red, blue, yellow, purple
Rob blue, purple
Trey yellow
How should I go about this?
During work this thing stuck me that i have a column A1 an amount like 12345663..now what i want in column B1 the same amount but except first 3 digits..for example instead of 12345663 i want only 45663 in column b..
View 4 Replies View RelatedI need a formula that will make every make every cell in column A unique. This formula needs to concatenate columns H, M, and N and display the result in column A. As you can see, you can get two instances of the same value (e.g., Dog:Brown:Small for A2, A9 and A10). For this reason, we then need to concatenate a counter number on the end that increases by 1 based on the total number of matching values ABOVE the cell in column A.
For example, I need a formula in cell A12 that concatenates H12:M12:N12 and then counts the number of "Dog:Brown:Small" instances above and then appends the largest incremented value +1. This would result in "Dog:Brown:Small:0004" in cell A12 and "Cat:Black:Large:0003" in cell A13. I will then drag this formula down column A and it will dynamically execute the formula against all column A cells above it.
I need VBA that will make those two columns:
1 A
1 B
2 C
2 D
2 E
2 F
2 G
3 H
3 I
3 J
into those two columns:
1 A,B
2 C,D,E,F,G
3 H,I,J
I'm trying to Concatenate values as per a condition.
The condition is being a member of group: 1
The result should be ACE but I need it to be presented in one single cell (like in C9).
(Pls see attached picture)
I tried to use SumProduct but it seems that this function handles only Numeric values.
I was able to present the result in a Range(!) of 6 cells with the Array-Formula:
=IF(B2:B7=D2,A2:A7,"") but this is not what I need.
I know how to solve this with VBA but as it is sort of an excercise I need it to be solved with sheets build-in Functions only.
I have a spreadsheet with two sheets, 'Sheet0' & 'Sheet1'. In 'Sheet0' I have a column named 'A_Sheet0' containing a list of numbers from Rows 2 - 50. In 'Sheet1' I have 3 columns, the first containing numbers 1 to 50, the second, 'B_Sheet1' containing a list of ascending numbers and the third is the column of interest. This column entitled 'Nearest Value' must read the corresponding value of Column 'B_Sheet1' then find the closest number to that from the numbers in 'Sheet0', column 'S_Sheet0'.
I have included a sample file with this thread, and in the 'Nearest Value' column I have manually input some of the nearest values for your information.
I had getting a hyperlink from a Shape to a Cell on seperate worksheets and the macro works fine:
View 4 Replies View RelatedI am somewhat new to excel and have a task I have a catalogue that I need to separate multiple printer models that all have the same part # into individual line items. This will save me weeks of work.
I have this:
Model
part
DCP-7020/HL-2030,2040,2070N/MFC-7220,7420,7820N
TN350
And want to end up with this:
Model
Part
DCP-7020
TN350
HL-2030
TN350
HL-2040
TN350
[Code]...
Or even this:
Series
Model
Part
DCP
7020
TN350
HL
2030
TN350
[Code]...
I have a list of sales enquiries 1600 rows long. Each has a brand recorded in cell J (from j4 above is headers)
I have 4 brands
A, B, C & D
I would like to copy the data from the master sheet into 4 tabs lables A, B, C & D depending on the brand recorded in J
I am trying to concatenate the values of many cells, not the formulas in the cells, to avoid having to repetatively paste special, value. Perhaps something like this: =CONCATENATE(VALUE(AA2):VALUE(AQ2))
View 3 Replies View Relatedwe want to add/concatenate the values of first column and show the result in next column. The problem is fully explained in the comment section of the sheet attached. But still if you are having in getting the problem
View 6 Replies View RelatedI have a column say column A. It has a pattern of cells where there is a code C10A3(alpha numeric) in say A1 and then in cells A2-A9 there are descriptions (alpha only). This continues uniformally down to cell 300. So every x amt of cells down a code appears and then below this for x cells is a description. What i would like to do is create a macro to concatenate the code waith each of the descriptions and paste it in the adjacent cell to the description. So....
A1 =C10A3
A2 =Global
A3 =America
A4 =South AMERICA
A5 =aFRICA
ETC
A10 =D05a9
A11 =Global
I would like to concatenate A1 and A2 and have the output in B2 (C10A3Global.)
And A1 and A3 with the output in B3 etc. This should continues until A10 (where the cell contains alpha numeric characters) where nothing is entered in B10 and then in B11 there should be D05A9Global....
The only difference between the description and the cell is numeric characters...
I am trying to come up with a user defined function to accomplish several things at once.
First, I need to lookup a reference value in one column and determine the value from another column (on the same worksheet) in the same row. Then, concatenate each "return" value (that isn't blank).
I have the following code so far, but my formula results in a zero. If I remove the On Error Resume Next, the formula results in a #VALUE error.
Public Function ConcatUnique(Separator As String, Ref As Variant, LkupCol As Range, _
RetCol As Range)
Dim lkup As Range
Dim ret As Range
Dim colDif As Long
Dim mCollect As New Collection
Dim i As Integer
Dim b As Variant
' Determine the number of columns difference between
' the lookup column and the return value column.
colDif = RetCol.Column - LkupCol.Column
On Error Resume Next
' Determine which lookup values in the lookup range match the reference value.
' When the lookup value matches the reference value, set the return range object
' to the cell in the return range (column) in the same row as the lookup value.
' Note we use the difference between the lookup column and the return column to
' determine the location of the return range object.
For Each lkup In LkupCol
If lkup.Value = Ref.Value Then Set ret = Range(Cells(lkup.Row, lkup.Column _
+ colDif))
' Store the return value in the collection object. Ignore any blank return
' values. Note we use the range value converted to a string as the key
' value.
If ret.Value <> "" Then mCollect.Add ret.Value, CStr(ret.Value)
' Loop through each cell in the lookup column range.
Next lkup
' Write each item from the collection and the separator to the final result,
' writing each value and the separator after the previous value and separator.
For i = 1 To mCollect.Count
b = b & mCollect(i) & Separator
Next i
ConcatUnique = Left$(b, Len(b) - Len(Separator))
End Function
Is it posible to do 3 seperate calculations in a single cell and desplay those results? For example in a single cell: A1+A2 | A3+A4 | A5+A6|
Where:
A1 = 1
A2 = 2
A3 = 3
A4 = 4
A5 = 5
A6 = 6
Results Display: 3,7,11.
I have cells in a column that record time in date. Unformatted it would appear as 19789.51407 but formatted to show time and date it would as appear as 3/6/54 12:20 PM. I am making graphs with the data and I want the title of my x axis to read "GMT Time (mm/dd/yyyy - mm/dd/yyyy)" or "GMT Time (mm/dd/yyyy)" depending on if the event spans more then one day. Using the following code, it almost accomplishes this.
Dim strDate As String
Range("CO3").Value = Range("A3").Value
Range("CP3").Value = Range("A" & Drag).Value
Range("CO3:CP3").NumberFormat = "m/d/yyyy"
If Range("CO3").Value = Range("CP3").Value Then
strDate = "GMT Time (" & Range("CO3") & ")"
Else
If Range("CO3").Value < Range("CP3").Value Then
strDate = "GMT Time (" & Range("CO3") & " - " & Range("CP3") & ")"
End If
End If
Where "Drag" is a variable that signifies the total number of cells in the column; ie the first and last dates. It compares the values and saves the results as a string. The problem is that when the string is saved it saves the date in mm/dd/yyyy hh/mm/ss. How can i separate the date from the time so I can have the title of my x axis appear as i need them?