I 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‎
In my assignment, I need to concatenate data from certain row and column ( The concatenate data will about 200 character). So once the concatenate cell change, it will copy the value to the next column.
View 9 Replies View RelatedMy 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 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 RelatedCan I concatenate two different rows in a column?
View 10 Replies View RelatedI am trying to concatenate multiple lines of comments into one string and place that string in the comment field corresponding to the start of each record set. I believe the code below is close, but I am receiving this error
Run-time error '91':
Object variable or with block variable not set
Debug points to this line
rngComment = . Cells(intCounter, 5)
I'm not sure what I am doing wrong.
Option Explicit.........................
I am trying to get a complete list of applications per device. I have the device list but it is broken down into seperate lines so i would like some sort of formula (not fussed what type as long as it gets the desired results!) that will look in column A and if the text is the same, to concatenate the info in column b, separated by a comma. So for the first device (4d713006) i would get 1 cell that would have the list of all 52 applications in it separated by a formula. This is so i can vlookup the device name from another sheet and pull through all the list of applications.
I have attached the data : example.xlsx‎
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 Replies View RelatedI need a formula to be placed in cell Col A, Row 1, that concatenates any and all cells in Col A, with a ';' separating each item. I only want this concatenation performed ONLY IF an adjacent cell in Col B contains the letter 'X.
Additionally, if the cell in Col A is null, then I the formula to ignore it, and not perform the concatenation.
Is there a way to do this formulaically?
I need to cocatenate anything between and including column 4 and col (unkown until macro is run)
is there a way to do this?
Basically need to add all the cells from 4th column to whatever col is. Example if col = 12
then I need columns 4 to 11 all rows together
In Column A, anytime a specific "text" appears, I want to concatenate the information in the row, and place it into worksheet Data-list under a specific heading.
[URL]
The formula I would use (not vba) is =IF(A3="FN",B3,""), then I just copy it all the way down. However there are 27K cells, which is why I want a VBA Code
Pivot Table. I have a large data set and have used a pivot table to summarise the COUNT of each row (Objects) for each Column Name (that is, I did what Pivot Tables do).
What I'd like to do in a new column is show a string of all the column names per row name.
I have a data set that looks like this:
Sheet3
A
B
C
1
Object
Description
Class
[Code]...
So I can pivot table to work with other analysis, but what I'd really like to get is a list of unique "Objects" (easy) with all the related "Class"es listed in column (or straight to concatenated). I can concatenate using Chip Pearson's StringConcat.
Here is an example:
Sheet3
F
G
H
I
J
K
1
Object
Countif
[Code]..
You can see Chip's Function in col K. What I'd like to happen is in Cols H to J be automatically populated. (For this example, I have typed in what I'd like the result to look like)
I considered using Vlookups (with helper column for 2nd & successive instances), INDEX/MATCH with SMALL to get 2nd+ instances and even lengthy IF statements. The main problem with IF is that in the real data set, I can have over 30 Classes.
I looked at Pivot table to see what I could do but haven't much experience in manipulating past regular feature.
I also took a cut&paste of the pivot table (as shown in part below) to see what I could do.
Here is a clip straight from the Pivot Table:
Pivot
A
B
C
D
E
F
G
H
I
J
[Code]..
And one from the cut&paste of the pivot table, which also shows the desired outcome in the right hand column:
Sheet2
A
B
C
D
E
F
G
[Code]...
I would also be fine using VBA, but its been a while since I did any basic coding so I wouldn't really stand a chance of starting from scratch.
I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
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)
Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.
Below is an example with the solution I am attempting to produce.
Sample Table
Name
Dept
Abbruzzese,James L
188100
[Code] ......
Output
Name
Dept
Abbruzzese,James L
188100, 231100, 600377, 600656, 600663, 600708
Abdi,Salahadin
600607, 600670, 600878, 600879, 710432, 710432, 710435
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%)
I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.
I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…
A = 2000 text
B = 1234 text
F = 2000 trailing spaces, text
G = 1234 trailing spaces, text
I = 10,000
Answer in column J
I want 20001234 = 20001234 = 10,000 for the whole column.
I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.
I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.
Example:
1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B
Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID
VOL
001
01
001
05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique)
VOL (concatenated)
001
V01, V03, V05
002
V01, V04
003
V06
PS: I have people using this table with office 2003, so compatibility is necessary...
I have this file attached,where I want a macro to concatenate 2 sheets data into one sheet,one after the other.
View 3 Replies View RelatedIs there a way to nest IF & And statements. I'm looking to concatenate a number of cells and seperate them with a space and/or comma but only if they contain data so need something along the lines of
View 3 Replies View RelatedIs it possible to run a macro (concatenate) automatically on a sheet when data is refreshed on that sheet from access database? It there a code that has to be written in vb?
View 5 Replies View RelatedI think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.
View 3 Replies View RelatedI have 2 workbookts: 1) Product; and 2) Buyers.
1) In the Products worksheet, every column has data about the books' description like:
A | B | C | D | E
Serial | Desc. | Author | Pubd. | year
Column A contains serial no. of the books, ordered from 1,2,3,4,5,6,7 to .....so on...
Columns B,C,D and E, have other details of the books.
2) In the Buyers worksheet, there are two columns;
A | B
Serial | Buyer Info
These are the details people who bought the particular book order by the serial no. of the book purchased.
Required
I need to make a new column in the first worksheet i.e. Product, say F, which I want to read all the buyers' info of that purchase the book with that serial no. and concatenate them together in the same cell separated by a comma.
I am attempting to use concatenate to combine two cells of data, but to only display the information if both cells contain information.
Currently using the following simple formula:
=concatenate(a1,a2)
However, I only want it to work if both a1 and a2 cells contain data. If only a1 contains information, but a2 is blank, I want it to calculate a blank cell.