Multiple Row Contents Into One Row
Nov 13, 2008
I have a list of Catalog which contains more than 5000 numbered items as shown below sample. Colum A is Material number and Colum B is the detailed description of material. The description in Colum B is written in one or more rows. I,e 90100100(A2) having 3 rows(B2:B4), the next material number (A5) having 4 rows(B5:B8)….. I need to apply a formula that Contents of all the rows of item A2 in one row in column C2 and contents of the rows of item A5 in one row in column C5…….till end of the list. Each row to be separated with a comma(,). Is there any formula or macro to fill for all the materials?...................
View 5 Replies
ADVERTISEMENT
Dec 14, 2013
i have attached a template spreadesheet that 20 people uses everyday. The template works fine but what i would like to do is write a code to clear all the content that manually entered beforeclose. For some reason code is not working properly because when i wrote code to clear the contents the buttons don't work . I also attached the spreadsheet.
View 4 Replies
View Related
Dec 29, 2012
I'm trying to put together functionality which allows the user to remove cell content within a selected row. I've chosen to remove the cell content rather than to delete the whole row because I need to maintain the 'Input Range'.
The code below is the script which I've put together to clear the cell contents.
Code:
Sub DelRow()
Dim msg
Sheets("Input").Protect "password", UserInterFaceOnly:=True
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
msg = MsgBox("Are you sure you want to delete this row?", vbYesNo)
[Code] ........
The code works fine if the user selects one row. However, if the user selects mutliple rows, although the text values are removed from all rows, only the 'Interior Color' is removed from the first rather than all and I'm not sure why.
View 3 Replies
View Related
Dec 12, 2006
I have the following code for about 20 sheets
Sheets("Multi CT 43").Select
Cells.ClearContents
Range("A1").Select
Sheets("Multi CT 61").Select
Cells.ClearContents
Range("A1").Select
Sheets("Multi CT 80").Select
Cells.ClearContents
Range("A1").Select
i'm sure there is a more simple way of doing this but I can't find a way that works.
View 9 Replies
View Related
Mar 17, 2014
My associates have saved about 2,000 .zip files in a single repository. Each zip file contains a .pdf, and a file called "metadata.xml" - the metadata files are small, only 1-2Kb.
What I'd like to do is import the contents of each metadata.xml file into a single workbook so I can build an inventory of the pdf files.
The full path looks like this:
Z:PrincetonGlobal DataFinancial DataFinancial DataWCFNDL_PRODUCTION128650_TH1X0_ProTechLLC_A_Eng_BBOT__20140317132245.195_bbot.zip
Everything up to and including FNDL_PRODUCTION is exactly the same for each file. Everything after, varies, and is not predictable.
I'm thinking there is some way to say "for each file in repository, import metadata.xml content..."
View 6 Replies
View Related
Mar 29, 2012
I need to clear the the text in the same cell ranges on multiple worksheets. on a regular basis.
F7:K13
Q7:Q13
Is there a simple way to do this?
View 7 Replies
View Related
Nov 16, 2006
what is described in the post clear "contents of rows based on cell contents in column" clear contents of rows based on cell contents in column
What is different is that for rows that I wish to leave untouched, I have in column A the text "Active". If that text is not present (""), then I need to clear contents for only certain cells in each row because others have formulas. For example, if A32 does not say "Active" I want to clear contents of cells D:32, G:32, etc while leaving the others in row 32 in tact. I need this to loop through about 400 rows. If it would make the macro easier, I can have the data in column "A" say "Inactive"/"Active" instead of blank/"Active"
View 4 Replies
View Related
Mar 20, 2014
I'm sure there is an easier way than copying and pasting each individual cell from one spreadsheet to another. Is there a way I can define a batch of cells (city, state, phone #) and copy them into the other spreadsheet so I don't have to do each cell individually?
Here is a picture to show what exactly I am trying to do.
SS.PNG
Also, the cells that belong in the same column and row on one spreadsheet are equal distance from each other throughout the other spreadsheet that has all the info in 1 column.
View 1 Replies
View Related
Feb 23, 2012
I have just started to learn how to use vba in microsoft excel. Over the past few days i have been creating a drawing register and have incorporated multiple routines based on searching this forum. Up until this point i have foud everything i needed on this forum, but now i cannot work out how to creat a routine that does the below.
The setup
*I have a folder called Zircon Plant
*That folder contains 3 folders 01. Superseded, 02. PDFs and 03. Documentation (the 01. Sup...etc. is the actual folder name, the 01, 02 and 03 isnt distinguishing the 3 folders)
*The Register is located in 03. Documentation
*The PDFs i want to hyperlink to are located in 02. PDFs
What i want to do
*i have 2000 rows of drawing names
*the drawing name in excel is spread over 5 side by side cells on each row (rows 21 - 2020) so for example the first row the information is in cells (H21, I21, J21, K21 ,L21)
*the cells i want to contain hyperlinks are V21 - V2020
so what i want is when excel opens up (i already have another script running at startup so i will be placing this after the other startup routine i have) i want it to search in folder 02. PDFs using the contents in (for example the first row (21) and colums HIJKL contain (H21)4CP(I21)-(J21)D(K21)-(L21)55000) each row and combine those cell contents to find the file name 4CP-D-55000.pdf. I then want it to go down every row until row 2020 untill it has added all pdfs.
(The second row is (H22)4CP(I221)-(J221)D(K221)-(L21)55001.....file name would be 4CP-D-55001.....third row would be 4CP-D-55002 etc...)
If such a pdf with that name doesnt exist, i want the cell (for example v21) to read "PDF not available"
based on a search here i have been able to do this for 1 cell, but dont want to do 2000 seperate routines. the current routine i have for it to work on one cell is
' Select PDF range
ActiveSheet.Range("u21").Select
' Promt PDF Hyperlinking
ActiveCell.Hyperlinks.Add ActiveCell, "I:DraftingAs Built4CP - PinkenbaE - ElectricallZircon Plant02. PDFs4CP-D-55000.pdf"
I also have multiple sheets. the first sheet is D - Documentation. I will eventually copy this routine 4 more times as i will be able to work out how to apply it to the other sheets
View 5 Replies
View Related
Jun 5, 2013
Collapsing function: I want to select cells that I want merged, and have them COLLAPSE into the first cell selected calls
Combining function: OR Select the cells I want merged, and allow me to pick a destination cell for the result COMBINE
I would like to add this functionality to all my spreadsheets in the future.
Excel-Forum-Data Collapsing sample.xls
View 1 Replies
View Related
Feb 28, 2014
I would like to automate the conversion of multiple versions of company names contained in cells to a single company name. i.e. XYZ Inc., XYZ Co., XYZ to all cells XYZ. My data file contains numerous versions of company names. I want them to all be the same company name so sorting data is easier. I now do this manually and it is getting tedious and time consuming. Can I build a table with the different versions that then chamges the cells to a single version that I choose?
View 1 Replies
View Related
May 30, 2007
Been trying to work this one out and have ended up banging my head against the wall.
Basically I have a spreadsheet, in the first column is the person's name, in the secon which team they are in and following this a cell for each day of the month with their availability.
The team name is a selection from a list, the availability is 1-6 (different levels of availability)
I'm trying to do subtotals at the bottom for each separate team, so the very last row each of the cells for each is added up:
Name Team 1st 2nd 3rd 4th 5th 6th 7th etc.
Mr X ___1st___1______2__1
Mr Y ___2nd_______2________5__5__5
Mr Z ___1st___1____________3__3__3
Total 1st: 2 2 1 3 3 3
Total 2nd: 2 5 5 5
So basically the formula in the last row goes along the lines of:
Add each cell in the column IF the team name = 1st
View 9 Replies
View Related
Dec 30, 2013
I have attached a workbook stating my problem.
file1.xlsx‎
View 10 Replies
View Related
Jan 14, 2013
I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!
I have provided a link to the example file below:
[URL]
View 4 Replies
View Related
Mar 13, 2014
how to copy the contents of multiple comment boxes and paste in a single comment box.
The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.
I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.
View 6 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Mar 25, 2008
I have a column in my .XLS whose contents I want to change depending on what the contents of the cells of a different column are. However, I only want two cells from the same row to be dependent on each other. So, for example, if $A$1 reads "1" then $B$1 should read "apple" and if $A$2 reads "1" then $B$2 should read "apple"; if $A$12 reads "3" then $B$12 should read "pavement" etc.
View 11 Replies
View Related
Oct 18, 2007
I am writing some VB code to change a comma separated text to individual columns
Selection.TextToColumns Destination:=Range("O14"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
"*", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
When I run the macro, it asks me if I want to replace the contents of the destination cells. How do I get rid of this prompt? I have tried to clear the contents of the cells prior to running the macro, and have also coded:
Application.AlertBeforeOverwriting = False
but, it still prompts me.
View 2 Replies
View Related
Aug 5, 2009
If “Dollar” is in cell B3 insert a row above it. If “Dollar” is not in cell B3 do nothing. Here is my current macro.
View 2 Replies
View Related
Jun 25, 2007
I have a formula that adds the contents of a row if a box is ticked. It then averages the result. This works fine, but now I only want it to add the contents of every other column before averging it. Can anyone adapt the formula below to allow me to do this?
=SUMIF(E73:P73,TRUE,E9:P9)/MAX(1,COUNTIF(E73:P73,TRUE))
View 9 Replies
View Related
Dec 18, 2009
Contents of Column C may well spread to over 18 characters, but column is formatted to be this wide - therefore if character length exceeds this, the data spills over into the next column. I don't want to use "Wrap Text" as I want to keep all rows the same height. I have toyed with this macro (which I'm sure came off this forum originally)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C5:C2200")) Is Nothing Then
ActiveWindow.Zoom = 90
Else
ActiveWindow.Zoom = 110
End If
End Sub
I quite like this as I can format Column C to "Shrink To Fit" and then read the potentially tiny font by clicking on the cell
What I'd really like though would be to keep the font the standard size for the sheet, but with a click be able to expand the cell to show the contents. I know that a simple cell click will show the contents in the formula bar, but it would be nice to actually work on the cell
On a related topic, again in the interests of neatness, is there any way to stop the "spilling over" of text into (in my case) Column D? This column is empty, and I can achieve my aim by putting a space into the topmost cell and then dragging down, but this seems a little messy!
View 9 Replies
View Related
Feb 5, 2010
i am trying to use clearcontents with the below code but i get an error,
With Sheets(1)
With .Range("J7")
.ClearContents Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
.Formula = "=H7-I7"
.AutoFill Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
End With
View 9 Replies
View Related
Aug 30, 2006
I have a column with adresses.
Cell A1 contents Road 12
Cell A2 contents Street 555
Now i want to split the content.
In cell A1 Road and in cell B1 12
In cell A2 Street and in cell B2 555.
Is there a macro that can do that?
View 9 Replies
View Related
Sep 10, 2013
I want the function of clear contents to work based on the value of B4. If I run the code with clear content part excel crushes.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B4") = "Basic" Then ''*** B4 is adropdown ***''
ActiveSheet.Unprotect
'Range("B10,F10,H10").ClearContents ''*** if I run this part the excel crushes ***'''
Range("B10,F10,H10").Locked = True
[Code] .....
View 4 Replies
View Related
May 2, 2014
If Cell A1 Contained 1,154,546,487,47,36,48,47,668 and so on. Using a Formula or Macro how can I get it to take each number(s) before each comma and put it in the next column on the same row?
So using the above example numbers you would end up with 1 in column A1 and 154 in column B2 and so on and so forth.
View 8 Replies
View Related
Dec 26, 2013
I have a cell that contains values such as "AAPL" or "0001.HK" (without the quotes). I would like to extract the content to the left of "." (if any). i.e. I want the cell to return "aapl" or "0001". What formula should I use?
View 9 Replies
View Related
Dec 24, 2008
I want to have a cell that looks at another cell based on a number that is in another cell. So imagine if you will: A1 contains the number 25. A2 contains the formula I can't work out. I want it to look at the row that is the number contained in cell A1, so in this case it would look at row 25.
Now I want to narrow it down further to a cell in that row but this will be a constant so lets say column F. So if A1=25 then A2 would display what ever is in F25. If cell A1 were 16 it would display the contents of F16 etc. The best I got was something like =IF(A1=>1,(F(A1))) but obviously that is wrong as it doesn't work.
View 2 Replies
View Related
Aug 28, 2009
I'm trying to work out if there is a way to rearrange the contents of a cell. Basically, I have names in each cell which have surname then first name and I want to have them reversed.
I know it would be easy if they were in seperate cells but unfortunately that's not the case. Is there a way to do it? If it's any help surnames are in upper case and first name in proper case.
View 11 Replies
View Related
Feb 10, 2010
I am looking for a way to remove text from a cell if it is 2 characters or less but do nothing if the text is longer than that. Is there a way to do this with either a function or an advanced find?
For more detail, I have a list of middle initials and last names. The cells are either a middle initial by itself like "D." or they are a last name like Jones. I want to go through and delete the cells that have the middle initial and leave the ones with the last name. So if there is a function that will evaluate the cell, and if it is 2 characters or less, then delete the contents. If greater than 3, leave it alone.
View 3 Replies
View Related
Feb 9, 2013
About half of the time when I move the contents of a cell I get the message "The Clipboard cannot be emptied. Another program might be using the Clipboard"
View 14 Replies
View Related
