Merging Table Cells With Text Under Certain Conditions
Aug 31, 2009
From a supplier I will get a spreadsheet including the the following Information which I need to convert into an url including trailing slash (/) for an online store.
Column A = Value of Main Cartegory (e.g. Holidays)
Column B = Value of Subcategory_1 (e.g. Christmas Decor)
Column C = Value of Subcategory_2 (e.g. Candle Holders)
Those raw data are without any trailing slashes.
Now I want to create a column e.g. Column D (product_url) which should be a summary of column A thru C but if there is only a Value for Main Category
there should be no trailing slash,
if there is a Value for Main Category and a Value for Subcategory_1: no trailing slash after Subcategory_1 but trailing slash between Main Category and Subcategory_1.
The result should look like this:
Holidays (Main category)
Holidays/Christmas Decor (Main category & Subcategory_1)
Holidays/Christmas Decor/Candleholders (Main category & Subcategory_1 & Subcategory_2)
Is it possible to create something like that in Excel at all since all values will only be text and the trailing slash is actually functional for Excel calculations but in this case it would have to be treated as "text".
View 8 Replies
ADVERTISEMENT
Jul 19, 2014
I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.
Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.
It is non-numeric text, and i've pasted --> values to remove formula issues.
View 1 Replies
View Related
Jun 14, 2008
I am trying to merge text, in four adjacent cells in the same row (say cells A1,A2,A3 and A4), into a single cell (say cell A5).
I would like a comma or full stop and then a space between each item merged (cell A5).
The text to be merged may appear in any one of the four cells (cells A1 to A4). Those cells without text are blank.
Only where all 4 cells are blank, will I need cell 'A5' to indicate this.
The formula needs to be relative as I will need to copy the formula down the spreadsheet so that it applies to additional rows.
View 9 Replies
View Related
Jan 26, 2010
I need to combine or merge text from cells in 20 rows of data into a format able to be printed. This is evaluation data for a class of 20 students, where each row is one students response to the evaluation, with the last cell in the row containing their comments (text data that may be blank, or up to several sentences in length). Here's what I'm trying to do:
Combine all 20 cells into one cell that can be printed (with word-wrap turned on).
Add a blank line between each comment.
Disregard any blank cells so extra blank lines are not added.
View 14 Replies
View Related
Jul 19, 2013
is there a formula or macro for merging identical text cells in a column?
I am looking at data relating to familes with a row for each family meber therefore the address cells for each family are identical. I would like to merge these cells for ease of reference when printing the data to be used as handouts for staff attending the households.
View 8 Replies
View Related
Mar 12, 2014
I have to prepare sheet 2-sales for my job and I cannot find the way to fill the price and sales results depending from the three other inputs required of the table.I belive we will need the functions addif, match, & others but still can't make it. Another tag when there is no result it needs to show "No existe producto".
View 3 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
Jul 16, 2014
Any better way than to look up all 300 + rows and see if row already exist.
I need the macro to look if row A as matching text then see if texte matches in Column B, if yes then add new columns and copy entire row. If no match found add new row and copy content to sheet #2.
I don't want it to overwrite the existing information already in sheet 2
View 3 Replies
View Related
Jul 30, 2012
I have a folder containing text files. Any way by which I could merge all the text files into one file - one after another?
View 5 Replies
View Related
Mar 4, 2009
How can I merge into a single cell from a range of selected cells that contain text. If the range of cells do have empty cells it should be smart enough to skip over or not to create an additional space. Any idea how can this be done? Macro or formula?
View 9 Replies
View Related
Jan 19, 2009
I have a column with data, the cells go data, blank cell, data, blank cell, data etc.
The row count will vary.
What I need to do is go through the non blank cells in the column and merge the them with the blank cells beneth them.
ie A2 = Apple
A3 = blank
A4 = Pear
A5 = blank
run the code to get
A2 and A3 = Apple
A4 and A5 = Pear
View 9 Replies
View Related
May 25, 2007
I have data in two separate cells as follows:
Cell A1 = 7890
Cell A3 = 1
In Cell A4 I want to merge the figures as following figure:
789.01
What type of formula would I need to create for this to work for me?
View 9 Replies
View Related
Jul 3, 2013
sample merging file.xlsx
I have a data array of 7 col and 256 rows. I want to focus only on the first 2 columns though. Here is what is going on. In the first column I have numbers from 1-256, these numbers are merged to the appropriate size to accommodate the data in column 2. However, the data in column 2 is not merged, it consists of 1,2, or 3 lines of text. Excel deletes the text in the rows below 1 if I try to merge, excel does not allow multiple row merge selections. What I am looking for is a formula that looks at column 1, finds how many rows are being merged in column 1, then looks at column 2 and identifies the number of rows needed for merging and displays that text in only one row. Basically, it's a formula that merges multiple rows of text in col 2 depending on the number of merged rows in col 1.
No.Customer/Operator
1Fairbanks Morse /
Beloit, WI
2H+H Umwelttechnik /
[Code]....
View 1 Replies
View Related
Nov 23, 2013
I'm trying to merge cells in a row (row 1, in the example below) that contain the same values. For example:
A A A B C C D D
into:
A B C D
I've used the following code, and it actually seems to work, but it gives me a runtime error and I can't figure out why.
VB:
Sub MergeCells()Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("A1", Cells("1", Columns.Count).End(xlToRight))
[Code]....
View 5 Replies
View Related
Jan 14, 2009
I need assistance with a formula two merge data of two cells together. For instance I have a column 'first name' and 'last name' and I want to create a column of just 'name' and concatinate the data eg: John Smith. I have roughly 175 cells to do this for.
View 5 Replies
View Related
May 27, 2012
Merging cells where the row is a variable. The code has two loops that consolidate date from two other sheets, it works fine but as you can see it puts the data into columns A,B and C,D respectively. My goal is to combine both into the A,B columns, which I can do very easily. My problem is that between the loops, marked by "HERE" i want to skip a line, merge cells A and B on whatever row that maybe and set an header.
Code:
Workbooks("Discovery Errors.xls").Sheets("Totals").Range("A3:U60000").EntireRow.Delete
Workbooks("Discovery Errors.xls").Sheets("Totals").Range("A3:U60000").EntireRow.Font.Bold = False
ActiveWorkbook.Sheets("Disc_Nodes").Activate
ActiveWorkbook.Sheets("Disc_Nodes").Range("A1:U60000").Sort Key1:=Range("J1"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
[Code] ........
View 1 Replies
View Related
Jun 19, 2014
How would I combine the data in 30 cells into one cell.
They all 12 digit numbers and I need them separated by a comma.
View 2 Replies
View Related
Mar 25, 2008
how would i run a macro to merge duplicate cells in a column
For example, i have lots of data in columns A, B and C
however some of the data in columns A and B is the same. Column C data never changes. so to make it easier to read, i would like to merge these cells (which are the same) together?
-ColumnA-
Cat
Cat
Cat
Dog
Dog
Dog
Dog
Mouse
Mouse
Mouse
Mouse
Mouse
would become.... (with all the other data the same)
-ColumnA-
Cat
Dog
Mouse
I dont want to lose any of the rows, as the data in column C is always different. I just want to merge the similar ones, so its easier to read.
Its a bit like this, but i cant even come close to getting this to work:
[url]
View 9 Replies
View Related
Feb 8, 2007
Is it possible to write a macro whereby the data in numerous cells in a row, say A1:K1 are copied into one cell, L1 then the cells from the next row, A2:K2are copied into the same cell, L1 but on the line below in the cell. I know the above doesn't make much sense so i've attached an example.
View 5 Replies
View Related
Sep 6, 2013
1) I am trying to find a matching value in a table. I have a 5*252 table that starts in B2. So the table goes from B2 to F253. I am trying to match a value in column D (column 3 in the table and column 4 in the spreadsheet) with a value in Column F (column 5 in the table and column 6 in the spreadsheet). The original value is in cell 222 in the table and cell 224 in the spreadsheet.
I tried the LOOKUP, VLOOKUP and HLOOKUP functions, but they return nothing but N/A. I use a reference cell or just the value for the lookup value but it does not work. I do not know if I am using the wrong formulas or what, but I have tried various permutations. (question 3 in excel workbook )
2) I am trying to calculate the standard deviation of a column D (contains both numbers and text) divided by column B (first in table). Excel does not appear to have a custom division formula. It has a remainder and a quotient formula which does not apply here. The problem is that the STDEV function does not allow for a division sign (/) in the formula. the STDEV function does ignore texts.
Is there a way for me to divide a cell ( containing both letters and numbers) by another cell just containing numbers and then taking the standard deviation of those values all in one formula?
View 1 Replies
View Related
Oct 12, 2012
I want to merge some cells using a criteria. To be more specific, let's speak on the file attached.
Beginning with the P5 cell I want to merge to the right groups of 4 cells for each name from the column J. Therefore the first group of merged cells will be P5:S5, the next group will be T5:W5 and so on until reaches 11 groups of 4 cells. The last group will be BD5:BG5.
I tried a script like this but it says that there is a syntax error on the "Else" instruction:
Range(Range("AB5").End(xlToRight).Offset(0, 1), Range("AB5").End(xlToRight).Offset(0, nrCat)).Select
VB:
Dim i As Long
nrCat = Range("O2").Value
For i = 1 To nrCat
If Range("AB5") = "" Then
[Code] .....
View 9 Replies
View Related
Mar 30, 2013
I have a spreadsheet of barcodes 114,618 rows long. I have a program that requires me to enter in each and every barcode in a certain format. This is the format that it requires me to place the barcodes into.
23423432, 23432342, 234324323, 234322344, 432432432, .............
so and and so forth.
Only problem is my POS system exports the barcodes in a format that looks like this:
545554545435
345435435435
354543534534
354534545453
Now I have a Macro to add a comma to the end of these barcodes. That part was a breeze, however, I have hit a brick wall when it comes to placing them into an ongoing sentence type format, that my program needs. I have found several ways to combine and merge and "justify" the content, however these typically only cover small amounts of content, and doesn't even come close to hitting anything over 100,000. I don't know if maybe I need to be using another program in tandem with Excel.
View 3 Replies
View Related
Sep 11, 2013
How can I let cell F5 for example contain both the texts or contents of cells A1 and B1? Is there a function that can do that purpose?
View 5 Replies
View Related
Jan 29, 2014
For my work I need to delete duplicate rows with the same name but keep the information in the (original) cell.
for example I have:
Cells: C D E F
Row 1 SoccerPremier League Holland
Row 2 SoccerPremier League Germany
Row 3 SoccerPremier League Poland Poland
I want to keep row 1 and add the data from row 2 & 3 with the data in the same cells so it will be:
Cells: C D E F
Row 1 SoccerPremier League HollandGermanyPolandPoland
As cells D, E, F are empty I would say you can just merge the 3 rows by selecting them and merge.
View 3 Replies
View Related
Sep 10, 2009
I am programming VBA for Excel and I have a problem with automatic popup messages. I would like to answer Excel's request "Selection contains more than one value. If cells are merged, only the first entry will remain" with OK. The cells are sorted, so they contain the same value, hence I want to merge them. Of course I could work around and delete every entry except for the first before merging, but I would like to know, how to answer this popup automatically, so that the user does not get to see it.
View 7 Replies
View Related
Mar 2, 2005
I am trying to merge two cells that have the exact same content in each cell. Everytime I do this, I get a pop-up warning that Im trying to merge cells with content in them and that only the upper-left content will remain. This is exactly what I want, without that pop-up, as I have to do this for multiple columns at a time.
View 2 Replies
View Related
Oct 3, 2013
I am trying to do the following:
I have about 50,000 rows of user info, something like the below -
ID
First Name
Surname
Description
1
John
Smith
Associate
2
Rob
Page
Associate
What would be the best way to go about this?
View 3 Replies
View Related
Jan 31, 2007
I have cells that contain number that have dashes
For example
Cell M contains the number 001-1234
Cell N contains the number 1234567 (I have formatted the cell to appear with a dash at the beginning -1234567
Cell O contains the number 891 (I have formatted the cell to appear with a dash at the beginning -891)
I want to combine the three cells and have the number look like this
001-1234-1234567-891
Is there a formula that will do this
I have tried the Concatenate to combine the cells but it just combines the numbers and won't let me format the number as I want it.
View 9 Replies
View Related
Jul 7, 2008
How would I go about merging cells with the same values AUTOMATICALLY?
For instance in cells A1:E1, each cell contains a value of 4 except E1 so I require A1:D1 merged.
View 9 Replies
View Related
Oct 24, 2013
I have a spreadsheet where I am trying to add up codes and quantitys, after this I want the repeated info deleted. For example;
12.004RWHB 4 Row Wooden Handle Welders Brush
12.004RWHB 4 Row Wooden Handle Welders Brush
20.004RWHB 4 Row Wooden Handle Welders Brush
24.004RWHB 4 Row Wooden Handle Welders Brush
4.004RWHBSS4 Row Wooden Handle Welders Brush S/S
10.004RWHBSS4 Row Wooden Handle Welders Brush S/S
12.004RWHBSS4 Row Wooden Handle Welders Brush S/S
So what I want to do is add the numbers up so I can get one value. For example, 4RWHB I want to add them all up so that it totals 68 still shows the code and description but then only shows 68 4RWHB 4 Row Wooden Handle Welders Brush and not the same line repeated. I want to condense all the information down so where there is same model numbers I can add it up and it doesn't give me multiples but summarises it down into one line per model number.
View 1 Replies
View Related