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 RepliesI 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
I'm trying to Concatenate E4 through E60 then Copy Column B,C,D,F,G to Single row then Sum H Through M. I've got My code Concatenating E But have not been able to get the rest.
Sub JoinCarts()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As String, i As Long, j As Long
Dim MyStr As String
Set ws1 = Sheets("Count Sheet Esman Greenhouse's")
Set ws2 = Sheets("Count Sheet Print")
j = 4
With ws1
LR = .Range("C" & Rows.Count).End(xlUp).Row
For i = 4 To 60
If .Range("C" & i).Value = .Range("C" & i + 1).Value Then
If .Range("D" & i).Value = .Range("D" & i + 1).Value Then.............
I have a spreadsheet of inventory items that I am trying to prepare to import into Quickbooks. There are items in this sheet that are subitems of a parent item. The subitems have the same ID number (col. C in the attached sample), different quantities and different descriptions than the parent.
I need to to concatenate the Item Lookup Code (col. D in example) and the description, separated by a colon. Then I need to copy everything else in the parent record, except quantity and description, into the child records. There are close to 20,000 items in this sheet.
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 get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.
I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
I have a spreadsheet which is updated daily. Row A of the sheet has the date in it, and every day a new column is created for the that set of data. I have the below code which works at the moment:
[Code] .....
I want to use this same code on another spreadsheet to do the same process (I need to copy and paste 4 columns (A,B,C + D, into E, F, G & H, then tomorrow it will copy E, F, G & H into I, J, K &L etc etc)). The problem I'm having is that A1:C1 is a merged cell, then D isn't (used as a border to separate). So when it is copied I need to select the merged cell columns and column D (i.e. A:C & D on day 1) and paste it into E:H with E1:G1 merged.
I'm trying to write a program for work. One sheet (whereiseverything.xls) will have a list of parts column E and where it is in the process column (k). Column E of this sheet will have multiple part numbers. (Sometimes duplicated.) I would like to figure out how to write a code to Auto filter (whereiseverything.xls) sheet and copy only one P/N from (whereiseverything.xls) column E onto another Workbook Worksheet (Commit status.xls) column. After that the sheet must copy all of the locations of that P/N from whereiseverything.xls column K into and under the P/N of the Commitstatus.xls.
It will continue to autofilter and copy from where is everything, the "one" p/n and all of its locations into another empty column of Commitstatus.xls until it no longer has part numbers to autofilter on whereiseverything. I am extremely green on VBA programming but here is my first attempt.
Windows("Where is everything commits .xls").Activate
Windows("whereiseverything[1].xxx").Activate
Columns("D:D").Select
Selection.AutoFilter
Windows("Where is everything commits .xls").Activate
Windows("whereiseverything[1].xxx").Activate
Selection.AutoFilter Field:=1, Criteria1:="7516113-905" \<--this Is one p/n
Columns("J:J").Select
Selection.Copy....................................
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 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 have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
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
I have a time card report which will record the time spent on work for each employee. Each month, i have to generate the time card report and copy the data to my master file. Is there any code that can auto copy the time card entry directly to the master file? I only need some of the entry on the time card report, some of the column can be ignored.
I was thinking of creating a macro that will prompt me to choose the file to import as there are different file for different individual. Attach here with the master list (Demo.xls) & the time card for one individual (Nov-KTTHAM.xls) for reference.
I want to copy the data on column B of the time card to the column A of the master list, column C to column B, column D to column C, column E to column D, column F to column E and column G to column G.
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
I have a column in two different worksheets that contain the same information that I want to match.
Raw Data - worksheet 1
Data - Worksheet 2
Column A - ShopperID
Same data is in both worksheets, column named the same in both worksheets
Raw Data worksheet contains email addresses in Column J that i want to extract and copy to column I in Data worksheet if there is a match on Column A.
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 have a dataset A1:E9, where A2:A9 is the object names and the remaining column (B2:E9) is the value of the object in the same row.
The object ID in A2:A9 has various names, and i want to copy certain data value from B2:E9, for only certain object name.
On the file attached i only want to copy data in Column C & D ( in the same row) of certain object name & then paste the data in to different working books.
I am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies View Related