Inserting Lines Based On IF

Aug 13, 2008

I have an amount of data split into various fields. I need to do something similar to the following:

In each row I have three fields separated by columns: name, sector and specialty. There is only one name and one sector for each record, but possibly several specialties (of which there are fifteen types in total, so they do not appear in a consistent column. specialty 'i' could appear in columns 3, 4, 5 through to 17. ie, sample entries below:

sheet 1
a1 name a2 sector a3 specialty i a4 specialty iv
b2 name b2 sector b3 specialty ii b3 specialty iv b4 specialty x
c3 name c4 sector c5 specialty i c6 specialty ii

The thing is, for every specialty in a row, I need new records created, so the above would become:

sheet 2
a1 name a2 sector a3 specialty i
a1 name a2 sector a3 a4 specialty iv
b2 name b2 sector b3 specialty ii
b2 name b2 sector b3 specialty iv
b2 name b2 sector b4 specialty x
c3 name c4 sector c5 specialty i
c3 name c4 sector c6 specialty ii

View 9 Replies


Inserting Space Between Lines In Conditional Formatting

Dec 5, 2013

I need to create a spreadsheet that has approximately 1000 rows with the same exact information in each of them prefilled (as I use the "drag-down" method). I use this chart throughout the year to enter various bits of data in each row. I also use conditional formatting in each row. The company I work for wants to have an empty space between each row.
You can create the blank rows separately and then interleave them with the existing rows by sorting. To start, insert a new column to the left of the existing column A. Enter 1 in cell A1 and highlight column A all the way to the last row that contains data. From the Edit menu select Fill | Series and click on OK. Column A should now contain numbers from 1 to the total number of rows. Press Ctrl-C to copy these cells to the clipboard, click in the cell just below the last of them, and press Ctrl-V to paste. Now highlight the entire data area, including the new rows with just a number in column A. Select Sort from the Data menu and choose the No header row option in the resulting dialog box. Under Sort by select Column A, under Then by select column B, and click on OK. Finally, delete column A. You now have a blank row after every one of the original 1,000-odd rows."

This works great for the data that I copied (with the drag down method) to all the rows. However, this method does not insert an empty line in between all the conditional formatting I have throughout the spreadsheet. This is my conditional formatting formula I have "manage rules" which is under "conditional formatting" =NOT(ISBLANK($A6)) (*please take note that this is selected for 1000 rows.) Maybe there is some kind of change in the formula that I need in order for the conditional formatting method to be copied onto every 2nd line of the 1000 rows".

View 10 Replies View Related

Inserting Lines Automatically On A Daily Basis

Apr 14, 2009

In cell b5 I have =today() which automatically updates the date on a daily basis. What I would like to happen is for each day there is a new line is automatically inserted with the date so:

Sat26/04/2008gone off sickFri25/04/2008Thu24/04/2008Wed23/04/2008Tue22/04/2008Mon21/04/2008Sun20/04/2008Sat19/04/2008Fri18/04/2008days holidayThu17/04/2008Wed16/04/2008Tue15/04/2008

I would also like any information on each of the line to move down when the new line is inserted. I am not really that good with macros or VBA but feel that is the only option to solving this problem. Also would everything still update even if the file wasn't opened for lets say the weekend. So it is constantly rolling.

View 9 Replies View Related

Inserting Lines, Takes Neighboring Formats But Not Formulas

Nov 15, 2008

I have a spreadsheet that I have people fill in. A few columns have VLOOKUPS. I want to allow people to insert lines in this spreadsheet and have the formula automatically copy in the new line. It seems to already have that behavior for formatting and conditional formatting. Is there a way to also have that for formulas when inserting lines?

Name City Assigned Salesman
john Mountain View (formula using VLOOKUP)
cindy Palo Alto (formula using VLOOKUP)
ronnie sunnyvale (formula using VLOOKUP)

Insert line between john & cindy. Formula doesn't copy.

I ask them to copy the formula but they often forget. If I can make it a little more robust,

View 11 Replies View Related

Inserting Rows - Need To Alter For The Blank Lines And Totals

Aug 13, 2009

I need to alter this code in order to do 2 things

1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc

2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5 ....

View 9 Replies View Related

Inserting 1 Line In Sheet1 To Enter 5 Lines In Sheet2

Jul 23, 2006

One has around 129 lines for the first years 2006 in sheet1.
The other links to the first 129 rows. Then I've linked the top 129 in sheet2 130 down for 2007 and so on for 5 years.

How would I add a line in Sheet1 then would insert 5 lines in sheet2 keeping the same order.


Name Rev Year
John 120 2006 Row 5
Jack 150 2006 Row 6

Sheet2 (linked)
Name Rev Year
John 120 2006 Row 5
Jack 150 2006 Row 6
John 130 2007 Row 134
Jack 160 2007 Row 135
John 140 2008 Row 263
Jack 160 2008 Row 264
John 150 2009 Row 392
Jack 170 2009 Row 393
John 155 2010 Row 521
Jack 180 2010 Row 522

The Rev changes by formulas in Sheet 1

View 9 Replies View Related

Create Sheets Based On Lines

Mar 25, 2014

result of command is like below:

YYY,24/03/2014,222 , 0002,ALARM TEXT DIFFER IN TIMEFORMAT , 1, 1,3.03030303
YYY,24/03/2014,333 , 0001,SCRIPT FAILED , 31, 6,93.9393939
YYY,24/03/2014,111 , 0003,ALARMS NOT RECEIVED , 1, 1,3.03030303
YYY,9999992867290,24/03/2014 ,333 , 0001,SCRIPT FAILED , 7, 1, 100
YYY,9999992813525,24/03/2014 ,333 , 0001,SCRIPT FAILED , 3, 1, 100


Now I'm looking is when opened in Excel, it should create a two sheets excel file 1) with the First Section and the 2nd with second section . based on headers included in file.which then will be used for 2nd stage of automating..

View 3 Replies View Related

Combining 2-4 Name Lines Based On Column B

Jan 8, 2008

in column A i have account name but it can be up to 4 lines (rows)

in column B i have the fund name which goes with the first account name row


View 9 Replies View Related

Macro That Duplicates Lines Based On Value Of Column?

Aug 22, 2014

I need a macro that duplicates lines based on the value of a Column. So in Attached excel D1 is 10 - I need it to create 9 entries for that line, so there are 10 total duplicate lines.

I had a Macro that worked that is in the document, I even had everything set up the same, which it is not now, but could not get it to work.

A Macro or a Finished document is fine, Just need this to produce raffle tickets for the church

Raffle Tickets.xls

View 2 Replies View Related

For Each Unique Value In Col A+B, Removing Lines Based On Column C

Jan 21, 2010

For each unique value of Order Number [col A] + Line Number [col B], I need a program which will locate the first instance of Next Stat [col D] = 530 and the first instance of col D = 540, deleting the other lines. The output will have two lines remaining for each unique col A+B combination, one where col D = 530 and another = 540. The data will always be sorted in date/time order prior to running this program. The actual data set has a varying number of lines, usually 1000+.

View 4 Replies View Related

Inserting Row Based On Condition

May 4, 2009

I want to insert a line wherever I encounter a number. I have a range of cells in column "A" with invoices listed and at the end of each customer I have the total. At the total line (the number) I want to insert a line. Right now I can't figure out how get the cell value to recognize a number from a non-number data type (bolded area)

View 5 Replies View Related

Inserting Row In Excel Using VBA Based On Y Value

Apr 18, 2013

i would to insert a row in excel using vba based on a y value

if insert.row = y(any value) then those many rows should insert

View 4 Replies View Related

Shorter Macro To Move All Lines Based On Cell Value

Jun 9, 2014

Basically I want to copy and paste all data in a row if the value in a cell is equal to something

I am currently using the below formula

The Value in red = Set tgt = ThisWorkbook.Sheets(Criterion.Offset(, 1).Value 'Pick the cell next to the cell containing the criterion).

But my issue is now that i will need this to work based on 37 values and having this macro 37 times with the value changed and 37 buttons will be difficult.

So I am looking for a way i can add multiple values each going to separate sheets.

View 14 Replies View Related

Inserting Pictures Based On A Cell

Nov 21, 2007

I want to be able to a place a picture on a workbook based on a cell. In column A, I will run a sql query to pull a list of items. In column B, I have the items formated thus they have a the .jpg. next to item of the picture i.e Hammer.jpg. whereas column A lists Hammer and Column B Hammer.jpg. In column C, I would like the pictures to reside in this column.

So far I have this macro but I can only get two pictures to come up even though my list has 4 items on it.

Sub pic()
Dim x, c, d, a As Long
d = 2
c = 40
For a = 2 To 6
b = Cells(a, 2)

View 9 Replies View Related

Copy Data On New Lines From A Source Sheet Based On Criteria

Oct 2, 2006

I have a source sheet which contains data. Within this data there is a column (column 3) called Name and I would like to take the data from this sheet and paste it onto a new sheet but in grouped format. So take all instances where it finds criteria 1, then 2 and so on and just paste it on a new line each time it finds a criteria. So far I have the following code to search

Sub loopy()
Dim rngTemp As Range ' the range variable declaration
Dim intCounter As Integer 'counter as integer
' the following loop goes through all the cells in column a up until the last used cell in the column
For Each rngTemp In Range("a1", Range("a" & Rows.Count).End(xlUp))
'increment the counter if the cell has value fridge
If rngTemp = "Fridge" Then intCounter = intCounter + 1
Next rngTemp
MsgBox "There were " & intCounter & " cells equal to Fridge"
End Sub

View 2 Replies View Related

Inserting Rows With Formulas And Formats Based On Cell Value

Jan 11, 2014

I am attempting to create a template to make time and dollar budgets for various projects easier to create (20 plus projects a year with varying number of employees).

I want to have a template in which you input the number of employees working in a cell (IE. 3 employees), and with that the table expands to have enough rows to have 3 employees data input. However, only insert the number required but not overriding "totals" row below. With the new inserted rows I would like to keep the same format and formula for the row it was based on.

Example :

week 1 hours
week 2 hours


View 2 Replies View Related

Inserting Page Breaks Based On Cell Text?

Feb 13, 2014

I'm working with a large report that includes ~6000 rows of data. Within the report the data is separated into a couple hundred town names - "Town of XXX" - and I need a page break at each town. Is there a way to insert a page break based on text in a cell? Should be an option in conditional formatting but... Need to avoid VBA.

View 1 Replies View Related

Inserting State Names Based On Telephone Numbers

Oct 22, 2007

I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.

There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.

Bleh, 555-555-5555, =ChkState(B2)

I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field

Function ChkState(pVal As String) As Long

Dim AreaCode As String
Dim StateAbrv As String

AreaCode = Left(pVal, 3)

If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function

I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.

View 9 Replies View Related

Inserting Rows And Copying Data Based On A Value In A Cell

Aug 14, 2009

On sheet "Create Package" in cell "AA14" I have a value (lets say 2).

I want it to go to sheet "Samples" and insert a number of rows equal to the value on sheet "Create Package" cell "AA14" (so 2 rows)

I have a header row in row 1, so I would like it to insert the designated number of rows beneath that.

Then I would like it to copy a designated number of rows (based off of the "AA14" value, so 2) from the "Create Package" sheet starting at row 66 and then paste special values into the new rows that were inserted on sheet "samples".

View 9 Replies View Related

Inserting Page Breaks On Relative Rows Based On Data

Oct 29, 2008

Each week I format a report that I receive that lists every product a particular employee closed. Its a list of each employee, the product and date. I'll have about 20-30 lines for each employee. I can easily sort by the employee name to have a nice list.

From here what I do is use a forumla to mark where each employee's name changes, then filter by that changed line and insert page breaks for each of these lines. Is there a way to macro this to save me time, having 270 employees takes alot of time still, because thats 270 manually inserted page breaks.

View 4 Replies View Related

Sum Lines Of VLookup?

Nov 22, 2012

I have a sheet which looks a bit like this


Cost A
Cost B


For each column with a header A, B (these are people), 1 in their column represents they works that day on a project. The day columns are days of the week, and people cost different amounts on different days. I need to work out the total cost for each person for the working period.

The cost for the first day for person A is =if(b2=1,vlookup(a2, f2:h8, 2),0)

How do I sum the costs for each day in a single formula without creating a cost per day column for each person i.e. sum each line of vlookups?

View 1 Replies View Related

Comparison Of The Two Lines?

Feb 19, 2014

I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :


To reach the conclusion I need to build six such functions:

The result is 4

Is there a single formula that will give the result ?

Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?

View 8 Replies View Related

Duplicate Lines

Jan 1, 2008

i have a list of about 2,000 rows of text going down vertically, but out of that 2,000 there's only about 1,500 actual items - the rest are duplicates.

how would i go about eliminating the duplicate strings of text quickly?

View 9 Replies View Related

Two Lines In A Msg Box On Open?

Jun 20, 2009

I need a msg to pop up when a worksheet opens. Pretty simple:

Private Sub Workbook_Open()
If MsgBox("My msg goes here!") Then
Exit Sub
End If
End Sub

But I need to be able to have multiple lines, maybe a short paragraph.
How do you get it to allow additional lines?

View 3 Replies View Related

Excel Min And Max Lines

Aug 15, 2012

In Column B I have some measurements. These are measurements. I chart them in a line graph. I also have my max and min over in some other cells. These aren't the min and max of column, but these are what I have set the min and max at. To make this clearer, my max is 27, but the data in Column B could be anything....20, 28, 35, 15, etc.

I want to plot my data in Column B just as I am currently doing. However I also want to plot the min and max on that same line graph and show them has horizontal lines going across. That way, if one of my numbers is above the max (or below the min), you will be able to see it on the line graph.

Here's what my data looks like. The Viscosity (column b) is what I'm charting on the line graph and you can see the min and max data that I want to plot as well.



Batch No.


[Code] .......

View 9 Replies View Related

Comparison Of Lines

Jan 22, 2007

There are four lines of numbers, in all line 8 numbers.

1 1 2 2 3 1 1 2
1 2 3 1 2 2 2 1
2 1 1 3 1 3 1 2
1 2 2 2 3 1 1 2

I would like to find a function that will examine, if there are similar lines according to my definition, and several.

my definition is 7

For example that I gave, the result in the wanted function needs to be, yes (Line 1 and line 4) and several-One time.

View 9 Replies View Related

Unhide All The Lines

Apr 26, 2007

I have a file that contains at least 35 sheets. All of the sheets have some lines that are hidden. I tried selecting all the sheets and unhiding everything but it didn't work because some sheets actually have filters in them and the filter line is also hidden.

View 9 Replies View Related

Combine Lines

Jan 10, 2009

I have a text file which is wrapped and i am trying to read it through VBA. I want to combine wrapped lines in one line

for e.g.:


View 9 Replies View Related

How To Put Worksheets In 2 Lines Or More

Jan 14, 2009

Is it possible to have worksheets in 2 or more lines at the bottom of excel window?

View 9 Replies View Related

How To Get Together All Duplicate Lines

Oct 22, 2009

How to get together all duplicate lines? ...

View 9 Replies View Related

Copyrights 2005-15, All rights reserved