How To Seperate Data In A Cell And Create New Rows
Jun 23, 2013
I am somewhat new to excel and have a task I have a catalogue that I need to separate multiple printer models that all have the same part # into individual line items. This will save me weeks of work.
I have this:
Model
part
DCP-7020/HL-2030,2040,2070N/MFC-7220,7420,7820N
TN350
And want to end up with this:
Model
Part
DCP-7020
TN350
HL-2030
TN350
HL-2040
TN350
[Code]...
Or even this:
Series
Model
Part
DCP
7020
TN350
HL
2030
TN350
[Code]...
View 5 Replies
ADVERTISEMENT
Jul 23, 2009
Attached file where i m not able to split data in seperate seperate coloumn
View 3 Replies
View Related
Nov 29, 2006
How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.
View 4 Replies
View Related
Feb 6, 2009
I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.
Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.
View 14 Replies
View Related
Aug 11, 2009
I want is a constant separation of two lists by 3 rows, one above another in one sheet. I have illustrated my desired change-and-results through the following images and hopefully they are self-explanatory.
I think its important to explain that I want the macro activated by the change of results in Column A in sheet2.
Current Sheet1
Current Sheet2
Current Sheet1 with Formulas
Current Sheet2 with Formulas
Sheet2's 1st Change (Time)
Sheet1 Response to Sheet2's 1st Change (Time)
Sheet2's 2nd Change (Replace)
Sheet1 Response to Sheet2's 2nd Change (Replace)
View 9 Replies
View Related
Jul 19, 2006
I have to look through an entire table and find certain criteria (eg. "STEVE'S PIZZA SHOP"). It might be in columns G or H, or it could also be in Columns C or something of that nature. What I'd like to do is search through the table, find each instance of said criteria and cut each row the criteria apperas in to a different sheet. I'd like to do this as a macro, so I can set it up for other criteria as well. In addition, if I can include in the macro a way to create a header row (which I'm pretty confident I can myself), as well as change the title of the sheet the information is moved to. I have looked up Do-While loops, For-Next loops, If-Then loops. I am at a total loss.
View 9 Replies
View Related
Sep 20, 2013
I have 2 sheets in one excel file.
I need a macro to check how many rows have value in sheet b and then insert rows accordingly in sheet A between cell B11:B100.
For E.g. if there is value from cell A1 to A150. than I want the macro to add 50 more rows in Sheet A between Cell B11 & B100 and also copy the formula that is there in cell N11 to Z100.
Data in cell A1 to A150 can be between 0 to 999
View 2 Replies
View Related
May 20, 2014
I am trying to develop a formula that will tell me whether an account has both Medicare and Medicaid or Medicaid HMO insurance. An account can have one or more lines, and one or more insurances. I manually completed the sample file in column 'D' (see attached) to come up with the desired output. The criteria is below:
-If the account has Medicare and Medicaid financial class, then it is a '5'.
-If the account has Medicare and Medicaid HMO financial class, then it is a '6'.
-Otherwise, it is a '0'.
View 3 Replies
View Related
Jul 28, 2014
I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)
for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked
View 12 Replies
View Related
Feb 25, 2007
I'm trying to put together a sheet to track football results. For simplicity sake, the sheet looks like this...
Home ; Away ; For ; Against ;
a ; b ; 2 ; 0
c ; d ; 1 ; 1
b ; c ; 1 ; 3
d ; a ; 2 ; 4
a ; c ; 1 ; 2
By using Autofilter on the home or away columns, i can obviously filter it in order that I can see all A's home results or all of A's away results, but by doing this you can't just filter the sheet in order to see the results of every game A plays in. I have a feeling that I need to do something with Advanced filter but have been playing around with this and not getting any joy. Can anyone advise on what I need to do?
Ultimately, my end goal would be to be able to create a drop down list where i would select the team from a list of all teams and this would automatically filter the sheet to their results only (and then I could use an autofilter to look at just homes or aways).
View 9 Replies
View Related
Feb 13, 2008
I have two seperate worksheets and I want to merge the data into one new worksheet.
example:
Book1 contains;
stock code
description
price
Book2 contains;
stock code
manufacturer
item name
I need to make one new worksheet which contains;
stock code
manufacturer
item name
description
price
I can not copy and paste because they are not in the same order and I can not sort to put them in the same order.
So I need to merge data using the stock codes into one new worksheet.
View 9 Replies
View Related
Dec 1, 2007
I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.
How do I write the function?
#TypeName Date
1CarsJohn1/12/2008
2CarsJane11/10/2007
3CarsMary11/2/2004
4CarsBob12/7/2003
1TrucksMike12/12/2007
2TrucksSandy1/3/2007
3TrucksDale12/14/2006
4TrucksVince4/8/2005
*
View 10 Replies
View Related
Nov 30, 2008
See attached spreadsheet. I need to have all the data which is held in 1 row in column A seperated into its own column automatically. The order i need the data to be in is as follows
Date(pink), Time(red), Racecourse(green), Race Type(black), Class Of Race(purple), Going(blue), Prize Money(yellow), Distance(grey), Number Of Runners(brown). the text in bracket is a key for the info for your ease of use.
View 3 Replies
View Related
Feb 9, 2009
I've got several sets of data all which link to seperate charts, the problem i'm having is that i dont want the chart to display the fields with zero values and with the amount of charts and data it is taking ages to re-source the data is there a way of getting the chart to ignore zero fields or of somehow sorting the data in a way only fields with values are shown
View 9 Replies
View Related
Oct 10, 2007
I have a data sheet of 190,000 lines. It is a telecommunications bill with has approx 15 columns.
I need to separate the spreed sheet into the respective "call types" and put this data onto separate tabs, at the moment I do it all manually with Excel 2007.
Is there a way of automating this separation of data
View 9 Replies
View Related
Mar 3, 2008
I have built a simple little logger forlogging insurance policies set up without permission, at the moment it logs all data in sheet2 of the active workbook. What I want it to do is when submit is clicked is to export and save the data to a seperate (central) workbook to collate all entries and not to save it in the active workbook. I've inlcluded the file and the code is as follows;
Private Sub CommandButton1_Click()
Dim ws As Worksheet, lRow As Long, Str As String
Set ws = Sheets("Sheet2")
lRow = ws. Range("A" & Rows.Count).End(xlUp).Offset(1).Row
'____error handling______
If TextBox1.Value = "" Or NullString Then
MsgBox "Ooops, it seems you haven't entered a policy number!", vbCritical, "Error..."....................
View 2 Replies
View Related
Apr 5, 2014
I am working on a schedule worksheet, the days are the columns and the staff member names go down the rows. I want to create a new cell value based on a condition on each row. I have been successful working with one row but how to efficiently do the same for each row.? I'm working with Excel 2010.
View 9 Replies
View Related
Mar 5, 2009
I have a list of names that I'm looking up, and I want to list the data relevant to each name in a seperate place using a VLOOKUP ,(maybe theres a better way to do it) but some of the names appear more than once, resulting in an error.
I have on the first sheet, Name, Customer, Amount and Variance.
On the seperate sheet (in the same workbook) I have then name with 4 VLOOKUPS underneath, trying to extract the data Customer, Amount and Variance. But, I get errors in all of them.
Is there anyway I can List all the data relevant to each name using a Lookup function or is there another formula I should be using?
View 4 Replies
View Related
Oct 5, 2009
I want to be able to look up values based on what I put in a seperate cell. For instance, I want to be able to type in 11 in cell B1 and have A1 return the value for E11. Then if I change the value to 10 in B1, the value for E10 would be returned.
View 3 Replies
View Related
Oct 27, 2009
During work this thing stuck me that i have a column A1 an amount like 12345663..now what i want in column B1 the same amount but except first 3 digits..for example instead of 12345663 i want only 45663 in column b..
View 4 Replies
View Related
Apr 21, 2009
I have a spreadsheet with two sheets, 'Sheet0' & 'Sheet1'. In 'Sheet0' I have a column named 'A_Sheet0' containing a list of numbers from Rows 2 - 50. In 'Sheet1' I have 3 columns, the first containing numbers 1 to 50, the second, 'B_Sheet1' containing a list of ascending numbers and the third is the column of interest. This column entitled 'Nearest Value' must read the corresponding value of Column 'B_Sheet1' then find the closest number to that from the numbers in 'Sheet0', column 'S_Sheet0'.
I have included a sample file with this thread, and in the 'Nearest Value' column I have manually input some of the nearest values for your information.
View 7 Replies
View Related
Feb 10, 2010
I had getting a hyperlink from a Shape to a Cell on seperate worksheets and the macro works fine:
View 4 Replies
View Related
Jun 26, 2008
I have a list of sales enquiries 1600 rows long. Each has a brand recorded in cell J (from j4 above is headers)
I have 4 brands
A, B, C & D
I would like to copy the data from the master sheet into 4 tabs lables A, B, C & D depending on the brand recorded in J
View 9 Replies
View Related
Dec 10, 2007
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.
View 5 Replies
View Related
May 12, 2008
I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.
View 9 Replies
View Related
Nov 2, 2009
Is it posible to do 3 seperate calculations in a single cell and desplay those results? For example in a single cell: A1+A2 | A3+A4 | A5+A6|
Where:
A1 = 1
A2 = 2
A3 = 3
A4 = 4
A5 = 5
A6 = 6
Results Display: 3,7,11.
View 3 Replies
View Related
May 20, 2008
I have cells in a column that record time in date. Unformatted it would appear as 19789.51407 but formatted to show time and date it would as appear as 3/6/54 12:20 PM. I am making graphs with the data and I want the title of my x axis to read "GMT Time (mm/dd/yyyy - mm/dd/yyyy)" or "GMT Time (mm/dd/yyyy)" depending on if the event spans more then one day. Using the following code, it almost accomplishes this.
Dim strDate As String
Range("CO3").Value = Range("A3").Value
Range("CP3").Value = Range("A" & Drag).Value
Range("CO3:CP3").NumberFormat = "m/d/yyyy"
If Range("CO3").Value = Range("CP3").Value Then
strDate = "GMT Time (" & Range("CO3") & ")"
Else
If Range("CO3").Value < Range("CP3").Value Then
strDate = "GMT Time (" & Range("CO3") & " - " & Range("CP3") & ")"
End If
End If
Where "Drag" is a variable that signifies the total number of cells in the column; ie the first and last dates. It compares the values and saves the results as a string. The problem is that when the string is saved it saves the date in mm/dd/yyyy hh/mm/ss. How can i separate the date from the time so I can have the title of my x axis appear as i need them?
View 3 Replies
View Related
Oct 28, 2009
I have two sheets in my workbook, and I am trying to reference one chart's value into the other. (attached)
I have referenced successfully by manually typing in the reference on row 2 for all values on Sheet 1:
Sheet 1 - C2 needs to reference Sheet 2 - B2. (Sheet2!B2) - no problem all the way across to F2. All those values are correct b/c I manually typed in the reference.
The problem comes when I just want to drag over that same formula to increment for the other cells. It's not incrementing the formula like I need it to.
So, Sheet 1, Row 2 shows how the correct values I need to pull over but was done in a manual way. Sheet 1, Row 5 shows how it increments incorrectly when I try to just drag/copy the formula over. What I need is for Sheet 1 - D5 to reference Sheet 2 - C3, but instead if I try and drag to copy the formula instead of manually typing it in, it increments when I drag reference D2 instead of C3.
View 2 Replies
View Related
Jul 19, 2014
I have created in VBA a formula to look for the last empty cell in the "SBV INFO" sheet and it works.
I now need the VBA to go the the DCWC sheet and only extract the data and amounts from Columns K and M where there is a value more than "0" in column M.
The the data in Column K where the value in Column M is more than "0" must go to the first empty sell (found in the SBV INFO sheet) as per the VBA and the corresponding amount in Column M must go to Column AA (also in the SBV INFO sheet)
In other words from the attachement I need the following:
Column M in SBV INFO sheet 1st empty cell (this will vary which is why i did the formula)
SBV DURBAN WHL
SBV George RET
SBV PEW WHL
Column AA
237,030.00
248,000.00
150,000.00
This might change in future. which means I would have to be able to transfer 1 to 15 of these names and amounts depending on the value in Column M from the "DCWC" sheet.
View 7 Replies
View Related
Oct 15, 2009
This is a new question based on another thread that contains a solution that I want to tweak.
That thread implements a row that filters the rows below it based on its inputs. Basically, an 'equals' autofilter, but you can type in the criteria in the cells above the column headers instead of going into the autofilter itself.
Here's what I need:
As a variant on that thread, I would like to modify the code to filter on anything that CONTAINS the inputs (not equals).
For example, my Array is A6:K300, with row 6 being column headers, I want Row 5 to essentially be a filter terms row, where I can enter partial phrases in row 5 and have the columns directly beneath them autofilter if the phrase is contained in any of the array rows. If I enter '123' in a cell in row 5, I want the filter results to show '123' and '123R', etc.
View 4 Replies
View Related
