Comparing Values In One Column And Inserting Multiple Blank Rows

Oct 13, 2006

I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example:

if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on.

Sub Macro1()
Const NumRow As Integer = 3
Dim StartCell As Range
Dim RowNR, NewCnt As Long
Dim RowCount As Long
Dim Count As Long
Dim intRow As Integer
Dim bFmtComplete As Boolean
RowCount = Application.WorksheetFunction.CountA _
(Range("A1", Range("A" & Rows.Count).End(xlUp)))
bFmtComplete = False
RowNR = 2
Range("A1:J1").Select
' Rows("1:1").Select
Selection.Copy................

View 4 Replies


ADVERTISEMENT

Inserting Multiple Cells And Shifting Down Column Multiple Rows In IF Statement

Sep 7, 2012

how to shift data in a column down multiple rows while in an IF statement. I am assuming you cannot just simply repeat the insert cell formula.

Here is my code - it's ugly but it was working when I just needed it to move down one cell:

lastrow = Range("A2").End(xlDown).Row
For i = 2 To lastrow
Range("B" & i).Select
If Range("B" & i).Value = Range("B" & i).Offset(-1, 1).Value Then

[Code].....

View 1 Replies View Related

Excel 2010 :: VBA Code For Inserting Text In All Column B-cells Of Multiple Selected Rows

Jul 11, 2012

Software: Excel 2010, Windows 7

What is the VBA code for inserting text in all column B-cells of multiple selected rows?

I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.

I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.

As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.

View 8 Replies View Related

Inserting 24 Blank Rows

Sep 13, 2008

Looking for assistance in inserting 24 blank rows in between a column of names. And if not too much trouble have the ability to enter the number of blank rows to insert.

Before:
Column A
name1
name2
name3
etc.

After:
name1
blank row
blank row
etc.
name2
blank row
blank row
etc.
name3
blank row
blank row
etc.

View 9 Replies View Related

Inserting Blank Rows And Sorting

Nov 21, 2012

Below is just a part of my very long list of statement that I posted into column A, B and C of excel worksheet:

10
67.12
03-Oct-12

10
85.16
02-Oct-12

[Code] ........

Any formula or macro that can automatically change to below expectation such as inserting 3-blank rows, totaling and sorting the date.

10
85.16
2-Oct-12

10
67.12
3-Oct-12

[Code] .........

View 7 Replies View Related

Counting Rows And Inserting Blank Rows

Dec 31, 2008

I have an excel sheet that has the A column populated with many different things.

I'm not sure how possible it is, but I'm trying to group everything up by 7s and putting a space in between the groups.

So...

ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7

Would turn into

ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7

HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7

QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7

View 9 Replies View Related

Inserting Blank Rows Between Different Series Of Data?

Sep 3, 2013

I would like to know how can i insert a blank row in between ACC# when ever a new series of data begins

name Acc # Balance Date
John 2222 200 june 2013
John 2222 344 june 2013
John 2222 700 Sept 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015

View 2 Replies View Related

Inserting Blank Rows Within Subtotalled Data

May 2, 2007

I've got a spreadsheet that I prepare for a group that has patient data subtotalled by physicians. The problem is that the group uses this data for data entry and wants a blank row between each different patient name within the physicians subtotals and I have no clue how to get there. Any chance there might be some VBA code that could accomplish this?

View 4 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

Excel 2007 :: Inserting Number Of Blank Rows Between Cells

Mar 5, 2013

I have a worksheet with four data columns A,B,C,D starting in row 2. I want to add between every row, exactly 11 blank rows. So if we have:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

the end result should be:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

Do I necessarily need a macro for this? Or is there another quick and creative way to achieve this result?

View 3 Replies View Related

Inserting Blank Row And Organizing Time-column

Jan 31, 2009

I'm a novice in VB and can't work out how to solve the following problem:

I have imported NMEA-data in text format from a GPS into Excel. This data is acquired in real-time at 10Hz, which borders what the GPS in capable of calculating. As a result the data isn't quite reliable enough - there are strings missing and some lines have been skipped by the GPS. This is a typical example of what sometimes happens: ...

View 15 Replies View Related

Hiding Particular Rows By Comparing Values In Array?

Mar 25, 2013

I need to hide the rows in an excel whose value is equal to the array list that has been already hardcoded...How to do this...

View 1 Replies View Related

Comparing Two Rows Replacing Values In Columns

May 29, 2014

I am facing a problem in evaluating a problem. I am referencing from the screenshot of excel file. I want to replace the values of Column-C for which column-A matches with column -G. And, the remaining will remain same.

Capture.jpg

I am also attaching the excel file.

View 5 Replies View Related

Comparing Cell Values And Moving Whole Rows?

Jul 2, 2014

I'm trying to compare the values of columns B and G and if any of the cells are G < B, I would like to move the whole row into the second worksheet. And only if possible, it would be awesome if the macro could check column A for duplicates before the value comparison and sum up the column B if there are duplicates. In my example row 6 and 7 on column A have the same suppliers code and the amount totals to 2.

In this case for example, the value of the third row of G is lower than B, and therefore I would like the whole row 3 to be cut out from worksheet 1 and moved to worksheet 2.

Original view of sheet 1:
SUPPLIERSCODE
ORDERAMOUNT
PRODUCTNAME
BARCODE

SUPPLIERSCODE
WAREHOUSE STOCK

And then cut out the rows in which G

View 9 Replies View Related

Comparing Data In Multiple Rows

Apr 20, 2009

I have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. The spreadsheet contains a lot of data, and is sorted by donor name, donation date (when the donation was made) and the feedback date (feedback on how the donation was used).

I'm looking to analyze how many transactions were made by a donor within 10 days of receiving feedback for a previous donation. The condition to check for is

(donor name is the same) AND (donation date >= feedback data for a previous donation) AND (donation date <= feedback data for a previous donation + 10 days).

How do I best perform this comparison? For instance, a donor may have made 20 donations, so I need to compare these donations amongst themselves and flag the ones where the donation date within 10 days of the feedback date for any other donation. I need to perform this exercise for each individual donor.

View 12 Replies View Related

Inserting Multiple Rows

Nov 2, 2010

I am new to VBA and am trying to insert 20 blank rows to my spreadsheet. I know how to add one row, but am not sure how to add 20 rows without repeating the same line of code 20 times.

View 6 Replies View Related

Copy Rows Based On Comparing Values In Columns

Nov 26, 2012

I'm new to macros and VBA so I am wondering if it is possible for a macro to compare values in column B of sheet2 and column B of sheet3 and for the values that DON'T match i.e. unique values to column B of sheet2, copy row from sheet2 to sheet4? This will aid my processing time drastically if I can copy all the rows from sheet 2 to sheet 4 where the model number in column B of sheet 2 doesn't appear in column B of sheet 3!!

View 3 Replies View Related

Inserting Rows Dynamically With Values From Other Sheets

Apr 12, 2007

We have a scenario like this:

1. Data (Let's say 5 fruit names are entered) will be entered in sheet1 (row wise)
2. Data (Let's say 5 flowers names are entered) will be entered in sheet2 (row wise)
2. Entered data in sheet1 and sheet2 should get automatically populated in sheet3 under respective headers (header 1 - fruits and header 2 - flowers)

Rules : 1. We will have Headers with one default empty row
2. With addition of every row in sheet1 or sheet2, a row should automatically inserted in sheet3 under respective headers and populate data.

View 6 Replies View Related

Inserting Rows On Multiple Pages

Mar 21, 2006

I am working on a database with Names and Volunteer Hours worked over about five years.

What I have is one sheet with names and info, and the respective sheets for each year and different events they worked that year.

on each year sheet I have only the first and last name, which is all that I need for personal info. I have these names on the seet using the sheet1!A1 formula. However whenever I add someone new they will not update the new row on the other sheets, they will simply ignore it. How can I make it respect my row updates?

View 3 Replies View Related

Macro - Inserting Multiple Rows

Feb 23, 2009

see the attachment. You will see that I have recorded two macros:

1) To insert a new row for new people.
2) To insert a row for new animals.

With regards to 1), I would like to create a macro that copies the contents of the row with the penultimate name in the people section, and for a new row to be inserted beneath the copied cell. Similarly, with regards to 2) I would like to create a macro that copies the contents of the row with the penultimate name in the animal section, and for a new row to be inserted beneath the copied cell.

The issue that I have is that the macros do not copy the penultimate cell in each section, but copy a particular row, say row 11 each time I want to insert a new animal. So if I had inserted numerous new rows for the people section, and subsequently wanted to insert a new row for the animal section, I press “crtl+sht+a” which copies the contents of row 11 and which is not the contents of the penultimate row for the animal section.

View 2 Replies View Related

Inserting Multiple Table Rows At Once With VBA?

Apr 4, 2014

way to insert multiple table rows (not entire rows) at once without having to make a loop? Let's say I want to insert 3 blank rows into my table at row 5. The way to accomplish this with a loop would be:

Code:
For x = 1 To 3
ActiveSheet.ListObjects("Table1").ListRows.Add (5)
Next x

Was wondering if there is a more simple way to do this (maybe even a one liner?)

View 1 Replies View Related

Comparing Values And Returning Different Column

May 31, 2007

I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.

HTML Column A Column B Column C
Date Number Who
3/23/2007 902-555-2596=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-678-0352=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~
Column A Column B
902-555-7958Big Bird
902-555-5100Daffy Duck
902-678-0352Elmo
902-555-5752Donald Duck
902-555-7420Bert
980-123-6621Ernie
902-555-2596Grover

View 12 Replies View Related

Macro - Comparing Values In One Column

Nov 13, 2009

I'll try my best to explain what I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.

Here is what I'd like to do via a Macro:

Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)

Example:

A
0
300
550
1200
1700
1900
2200
2000
1850

[Code]....

So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.

Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.!

View 9 Replies View Related

Macro: Comparing Values In One Column

Nov 13, 2009

I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.

Here is what I'd like to do via a Macro:
Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)

Example:
A
0
300
550
1200
1700
1900
2200
2000
1850
1750
1600
1500
1950
2500
4000

So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.

Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.

View 9 Replies View Related

VBA - Comparing Cell Below To Above And Inserting Row

Dec 27, 2013

I am currently using excel 13' and am having trouble inserting rows once the value in column b changes. My goal with this procedure is to insert two rows after each change in value in Column b (i.e. b2, b3, and b4, all equal 123 however b5 equals 124) Below is the code.

Dim cell As Range
Set i = Range(Range("A1:I1"), Range("A1:I1").End(xlDown))
Set bsort = Range(Range("B1"), Range("B1").End(xlDown))
Set ISort = Range(Range("I1"), Range("I1").End(xlDown))

[Code]....

'Need to Identify which cells are not equal to the one above in column B (bsort = range)

For Each cell In bsort
If cell.Value cell.Value - 1 Then
ActiveWorkbook.ActiveSheet.EntireRow.Insert
ActiveWorkbook.ActiveSheet.EntireRow.Insert
End If
Next cell
End Sub

View 2 Replies View Related

Inserting Rows Or Column In Excel?

May 2, 2014

macro for inserting rows and column in Excel.

View 1 Replies View Related

Comparing And Inserting Data From Another Worksheet

Jul 10, 2014

I have two worksheets.

1 worksheet I have a value and I need next to it the result i take from other worksheet.

LETTERS
AMOUNT
A
Result
C
Result
G
Result

[Code]...

The list goes on.

So Pretty much I need to compare the value "LETTERS" on the first worksheet with the array of letters from second worksheet and insert in the field of the Result, the value next to the correct find in the array of letters ...

View 9 Replies View Related

Comparing And Sorting Multiple Column Groups?

Feb 28, 2014

I'm am trying to take multiple column-groups and sort them by product ID number.

I am trying to put together a sales record for the past 26 months for around 2000 different items and sorting them manually would obviously be a nightmare.

I would like to sort them so that all the drugs with the same NDC (prod ID) have the correct data for each month displayed on the correct row for that NDC. Right now I have each set of data for that month sorted by product ID number but some months we used certain products and others we did not. Is there any sort of Macro that I could use? What I am trying to do I attached two files. The "before" and my desired "after".

starting point.xlsx
end product.xlsx

View 1 Replies View Related

Comparing Text Values In Two Cells And Adding 1 To Result Column

Apr 14, 2013

I have an excel file with some data in it. I have two columns with one of four words in it. For simplicity I'll call them RED, GREEN, BLUE, and Yellow. A few columns over I have columns "Same" "Good" and "Bad". I'm trying to figure out a way to get excel to put a 1 in the appropriate column, if cell A1 is Red I need excel to look at the word in cell B2 and put a 1 in the "same" column if A1 and B1 are the same word. If the word in B2 is Yellow I need excel to put a 1 in the "Good" column. If the word in B1 is GREEN or BLUE I need it to put a 1 in the bad column. The tricky part is each of the four words RED, GREEN, BLUE, and Yellow have a different set of words that will be called "good" or "bad". So if the word in cell A1 if Green for example it will have a different set of good and bad color words.

So it seems I need four different functions depending on what word is in A1. In the column next to these lists of words I have the date which I'll need to use to give me the sums of same, good, and bad for each day. But first things first. I think I need to use a combination of IF functions and MATCH function, but I can't figure out anything that works. Below is what I'm hoping it will look like when I've got the right formulas.

First column
Second column
Date
Same
Good
Bad

[Code]....

View 9 Replies View Related

Copying Only Rows With Data In First Column - Skipping Rows With Blank First Column

May 12, 2014

I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.

I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved