we 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 RepliesHere's what I have.. (on a much smaller scale)
http://www.jmetenterprises.com/produ...pics/excel.jpg
(notice how the lines that match are now even.)
[Edited by admin~ *Link* to large images, don't display them]
In A1
01234
In B1:k1
01 02 03 04 12 13 14 23 24 34
I am looking for a formula that will combine each digit together as a 2 digit value
Is this even posible?
The value in A1 could range from a 3 to 9 digit value.
What can I add to this existing formula to look for duplicate values in the entire column (D) and then concatenate the text of column (K) in each of those rows together?
Essentially I want to string all text in column K together, for each duplicate row of column D with the results in column L.
=IF(D14=D15,CONCATENATE(K14,K15),K14)
Excel user that has been manually computing a 6000 row spreadsheet. Here is sample data and what I need. Column A goes through the alphabet (A,B, C, etc.) and Column B is numbers from 1-65. I need Column C to count the number of rows for each alphabet/number pair. K, 28 has 3 rows; K, 33 has 1 row; L, 21 has 4 rows; etc.
Column A
Column B
Column C
K
28
3
K
28
3
K
28
3
[code]....
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)
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 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 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'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.
My issue is that I have 1200+ addresses to make more readable - the first column is the street name, the second is the post code(s) relative to the street - what Ideally I'd like to achieve is one row per street, the street name followed by the post codes... i.e. take this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG
Abbey SquareRG1 3BE
Abbey SquareRG1 3BQ
Abbey SquareRG1 3FB
Abbey StreetRG1 3AN
Abbey StreetRG1 3BA
Abbey StreetRG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP
Admirals CourtRG1 6SR
Admirals CourtRG1 6SS
Admirals CourtRG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL
to this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG, RG1 3BE, RG1 3BQRG1 3FB
Abbey StreetRG1 3AN, RG1 3BA, RG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP, RG1 6SR, RG1 6SS, RG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL
Writing a UDF for VBA as I'm getting frustrated by the concatenate function.
I basically want to create my own function that will concatenate values in column B, if the value in column A is correct.
I've been looking at this link [URL] ......
With a very simple data set as an example, I want the following output in column C:
pet
food
conc
cat
chicken
chicken,fish,catnip
[Code] ........
I think I need to create a cell based function which takes a conditional range (col A) and concatenate range (col B) as inputs, stores this as a 5 by 3 array and then returns the concatenated output I wish (by referencing the stored array) based on what value is in column A. A "concatenate if", if you like.
I had a problem finding something then deleting the column, I tried to alter the code from that thread to solve a new problem and I am close (I think), but not quite there. I need to find the column "Group" and merge it with the column "Sex" which is offset by 1 column. This code will go through and find the column "Group" and merge for the first cell, but will then get stuck in a loop just concatenating "Sex" to the end continuously. I need it to concatenate both columns entirely together. Any simple fix to what I modified? Previous thread should you be interested:Find Part Text On All Sheets & Delete The Column.
View 9 Replies View RelatedI 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 RelatedI 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).
I 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
I've made a "Budget Calendar" so to speak.., with my bills on the first sheet, and the Months in the following sheets. What I'm trying to do is say, for instance, the day is the 11th on the calendar, (there are two bills due on this day), and I have the bill scheduled to be on the 11th, can I use a formula to return what bills are due?
On the "Bill Dates" sheet, you can see where I'm kind of going with this...
On the August calendar, under the 11th, I want it to say "Internet, gas (utility)" in cell D20, or "Internet" and "gas (utility)" in D20&21, respectively.
I can get E20 to return the total bills due for the day using "=VLOOKUP("11th",'Bill Dates'!$B$2:$D$395,3,FALSE)", and it'd be awesome if I could get it to return them in different rows as well.
Finance Calendar.xlsx
I am trying to find 3 values (X,Y,Z) in row A and concatenate those three values in column A for the entire range below, say A2:A100.
View 9 Replies View Relatedi have 2 listboxes with numbers if user has selected choice in both then i want to enable okbutton. i tried in okbutton's mousemove: if listbox1.selected = true AND listbox2.selected = true then okbutton.enabled=true.
View 3 Replies View Relatedis 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
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 trying to merge column A1 (ABC) with column B1(XYZ), B2(LOL), B3(ROF) ..etc
with a simple =CONCATENATE(A1,"-",B1) , i can get "ABC-XYZ" , but when come to column B2 , i only can get "-LOL" , any solution ?
I have created vbscript that references a particular column and row. This cell is referred to many times in the scripting but the row may change from time to time. I would like to know how I can define the row value at the top and reference this row within my vbscript.
Dim row As Integer
row = 1
'vb script coding referring to cell
ws.Range("B&row").Copy Destination:= Sheets("Summary").Range("B1")
ws.Range("E&row").Copy Destination:=Sheets("Summary").Range("B16")
ws.Range("F&row").Copy Destination:=Sheets("Summary").Range("B17")
I tried to concatenate the row to the column but it doesn't seem to work. When I would like to change the row value I would like to only make the change where row is defined rather than within each line of coding.
how to concatenate whole column to a single cell in Excel. I have 3605 cells to be concatenated to a single cell
View 5 Replies View RelatedI have a pair of digits in col B, that I would like to match with the digits in in col D, and display those matches in col E. If possible I would delete the duplicates in col E, and show results in col F.
View 9 Replies View RelatedI wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?
View 4 Replies View RelatedI need a macro to be prepared to Concatenate serial and case separated by a "-"to find the Number column as shown in attachment ...
E.g. If serial is 27943900. After Concatenating it should be 27943900-001004
Example.xlsx
Can I concatenate two different rows in a column?
View 10 Replies View Related