Data Loop - Move Certain Rows Between Two Keywords

Sep 19, 2013

I have a workbook with part data. Each worksheet is an individual part with its data on that sheet. I'm trying to move certain rows between two key words to a separate wb/ws for the top key word. Data looks something like this:

Supplier 1
row
row
row
Supplier 4
row
row
Supplier 15
row
row
row

As I said, each sheet could have only one supplier or a dozen. The number of rows between each supplier also vary. I was trying something like this to do what I need to do:

Code:
DistiStr = "Supplier 1, Supplier 2, Supplier 3, etc etc etc"
DistiArr = Split(DistiStr, ",")
intDistiMax = UBound(DistiArr)
Set VendorWB = Workbooks.Add(xlWBATWorksheet)
VendorWB.SaveAs Filename:="Vendors"

[Code] .......

I see where my logic problem is: It's looping through a3 - lastrow, but I'm going one row extra each time, so when it goes to the top of the loop again, I'm one row to far starting off. I originally did this because if there's only 1 supplier on the sheet, there won't be another supplier name below it to give me my range.

View 2 Replies


ADVERTISEMENT

Selecting All Rows With A List Of Keywords?

Mar 15, 2014

I have a list of Products in my Excel file, I am trying to put them into categories easily. So what I am asking is there a way to select all rows with a certain key word in them so I can easily paste them into my other excel file?

View 1 Replies View Related

Delete Rows Between 2 Keywords In Column

Jun 26, 2008

I want to delete entire rows between two keywords.

Example:

Row 1 Connecting
Row 2 aaa
Row 3 bbb
Row 4 Unavailable

I want to delete rows 1 through to 3 and loop it until the worksheet is completed.

View 9 Replies View Related

Move Data From 127 Rows To The End Of First Row?

Jun 18, 2014

I'm trying to adapt a macro written elsewhere on this forum for my purposes. I need to move 127 rows of data into one row. The macro written does exactly what I would like, except that it only works for 3 rows, rather than 127. How do I get this to work?Here's what I have so far:

[Code].....

View 3 Replies View Related

Move All Data Rows Up 1 Row

Sep 6, 2006

I created a macro that is supposed to copy the contents of about 500 rows, and move them all up one row. One of the things that is contained in those rows are a bunch of links to Microsoft Word documents. When I try to move the 500 rows up manually (just copying, and pasting one row higher), it works fine. The link remains entact.

However, when I use the macro I recorded, the link no longer works. It looks like it is there (the text used to represent the link is there), but when you click on it, nothing is there. Here's my code....

View 9 Replies View Related

Move Data From Many Rows To 1 Row

Nov 17, 2006

I have attached a sample file and would like to have the expected format as shown in the attached. Would greatly appreciate if someone could show me the faster way of reformating the data instead of doing it manually.

View 3 Replies View Related

Formula To Move Data On Same Row And Deleting Empty Rows Between Data

Jul 17, 2014

formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.

What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.

See annexed file for expected results.I have preference to have a formula much more.

View 7 Replies View Related

Compare And Move Rows Of Data

Jun 28, 2007

I need to run the same database dump every day and find the differences. Both spreadsheets will have the same column headings, with Column A being the lookup value. Each spreadsheet will have many columns of data. I need to find the differences from Row 1 on spreadsheet A with Row 1 on spreadsheet B.

Example:
Spreadsheet 1, Column A value is 900026. Need to find 900026 in Column A on Spreadsheet 2. Compare all the data in each column to see what has changed. If anything has changed on Spreadsheet, copy the row from Spreadsheet 2 onto a new sheet and 'tag' which data has changed.

Also, again using Column A as the identifier, if there are rows on data on Spreadsheet 1 that do not appear on Spreadsheet 2, copy the entire row onto a new sheet and tag it as "Not on new datadump". And vice versa, is there are rows of data on Spreadsheet 2 that do not appear on Spreadsheet 1, copy the rows of data onta a new sheet and tag it as "Not on old datadump".

View 9 Replies View Related

Move Delimiter Data To New Rows

Jan 3, 2008

I ran across this macro in the posting " Move Adjacent Cell Into Rows, Same Column" and it is exactly what I need but when I try to use it I get a compile error on the Range("i1").Resize(n, UBound(b, 2)).Value = b. and several other problems. I also change "b" to "c" because this is where my data that I need to work with is located.

I have a similiar case much like the posting statement above where I have a spreadsheet on each row in cell "C" a EP CODE that have various information in it. My problem is that in the EP CODE column, it contains several EP CODE separated by semicolon (";") delimited . In order to serve my needs, each EP CODE instance needs to become a new row (record) whereas all of the information (except for the EP CODE) is the same and need to be repeated for each new row. Example: Before

Name Location EP code
WillisFremont 1;2;3;4;5;6
RaysSan Fran 7
SuNew York 8;9;10;11

After
namelocation EP code
WillisFremont 1
WillisFremont 2
WillisFremont 3......................

View 2 Replies View Related

How To Transpose / Move Data From Columns To Rows

May 18, 2013

So, I have a column with data on rows as follows below. I need to arrange the data below in such a way that I have on first column the company name, second column the contact person and so on depending on what data is found (tel, e-mail, website).

The data is on rows and I have separated each company/group of data with a row between them. There are in total aprox 200 companies/groups of data that I need to arrange as explained above.

I have tried with transpose but I have to manually do it 200 times. I have tried with an indirect formula but the companies/groups of data do no have same amount of info/rows e.g. some lack the phone number or other data.

if there is a method to save the time and not arrange them manually.

Actinote
Contact: Toine Kets
Managing Director
Str. Tache Ionescu 3, Et. 5, Apt. 10

[Code]....

View 7 Replies View Related

Move Data In Columns If Rows Contains Particular Text

Feb 24, 2014

If the row contains a text like ABC in multiple columns in a sheet it has to copy the whole column from that sheet and paste special into another sheet.

View 2 Replies View Related

Move Column Data To Row Then Delete Those Rows

Jul 23, 2007

We have a client that provided us with a text file that we imported into
Excel. Rather than have the entire record in one row, they have the record
in 4 rows.

What I need a macro or something to do is move award 2 award 3 and award 4 into the same row and after doing this delete the other rows.

Below is an idea how this looks.
1JohnSmithstreetcitystateZIPaward 1
2JohnSmithstreetcitystateZIPaward 2
3JohnSmithstreetcitystateZIPaward 3
4JohnSmithstreetcitystateZIPaward 4
5BillJonesstreet2city2state2ZIP2award 1
6BillJonesstreet2city2state2ZIP2award 2
7BillJonesstreet2city2state2ZIP2award 3
8BillJonesstreet2city2state2ZIP2award 4

What we need for our data to work is:
1JohnSmithstreetcitystateZIPaward 1award 2award 3award 4
2BillJonesstreet2city2state2ZIP2award 1award 2award 3award 4

View 9 Replies View Related

Move Blocks Of Data In 1 Column To Rows

Nov 15, 2006

Moving Data And Deleting Blank Rows. I am a complete newbie to excel VBA and require assistance (if at all possible). I have data in the following format in an excel sheet (I have a lot more rows but give an example for two rows)... in 1 column but I wish to separate it (i.e. move data along onto the first row in other columns) and delete blank rows.

For Example:

Col1
AAAA
BBBB
CCCC
DDDD
EEEE
AAAA
BBBB
CCCC
DDDD...............................

View 9 Replies View Related

Move Data From One Cell In One Row Into Several Rows And Copy Rest Of Row

Mar 2, 2013

I publish a green building mag, and we run an enquiry system for readers on our website. This is the link: [URL] .......

The idea is that readers tell us some info about themselves, including what types of products/services they'd like to receive information about, and we pass their details on to advertisers offering those products/services.

When someone fills out a form, we get data in Excel on their enquiry. It's all in one row - name, contact details, and then the products/services they've enquired about are included in one cell, separated by commas.

So for instance, suppose a listing currently came in as follows (column headings in brackets:

(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation, solar panels, windows

Essentially I want it to run like this:

(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) solar panels
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) windows

View 14 Replies View Related

Excel 2010 :: Macro - Move Data From Rows To Columns

Dec 13, 2011

I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.

Here is what I currently have:

Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS

Here is what I need it to look like:

Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS

There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).

I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??

View 3 Replies View Related

Excel 2007 :: Move Data From Multiple Rows To One And Delete?

May 2, 2012

I'm working in Excel 2007 and need to move data from multiple rows to a single row if the ID matches.Below is sample data I would be working with. I want to move data from columns F-U to the right of the original data in the row above it. I would also like to delete the rows that had data moved.

GIDSurnameNameEmployee Number OriginalDate of birthGranting ARE
Employing ARECountry Employing ARECHCM Supplier IDVehicle
Investmt. shares / Awards at termination dateMatching Shares
at termination dateTermination

[Code]....

View 3 Replies View Related

Extract Data From Worksheets Based On Keywords

Nov 15, 2009

I have a workbook with data extracted from our ERP (transaction sheet), and a list of cards (card list sheet) which ties the relevant cards to companies and vehicle.

I would like a macro to extract all transactions in the "transaction" sheet into a new file (see "unit" sheet), with the relevant company name, report dates, vehicle number, card no, date, time, polling point name, volume, unit rate. The "Amount" column will be the volume X unit rate.

Vehicle numbers can only extracted from "card list" sheet, and not found in the "transaction" sheet.

these are the details I need:
- all company's transactions details (not sorted in the "transaction" sheet) should be extracted into new individual company's excel file
- headings I need are shown in the "Usage" sheet (company name, vehicle number, etc)
- depending on the number of transactions, there should be a " subtotal" row below the last transaction (as shown) of each vehicle
- with all vehicles extracted from each company, a grand total will be added
- filename should be created with company name

View 9 Replies View Related

How To Move To The Next Row In A Do Loop

Nov 10, 2006

In a macro I made, I have a do...while loop:

Do While Not objXLworkBook.ActiveSheet.Cells(nSequence, 1).Value = gcstrEMPTYSTRING

.....bunch of code

Loop

In my file, the second row of data always contains blanks (i.e., I've got a HDR record, blank row and then rows of data). How do I make my code bypass that empty row of data and then continue processing all other records?

Should I put a "move to next record" in my bunch of code, and if so, how do I do that?

View 9 Replies View Related

Locating Certain Row Of Data Containing Keywords And Displaying Portion In Another Workbook

Aug 21, 2013

I have a workbook with a master data list including member names, member locations, member phone numbers, and various items checked out or on loan to members. I then have multiple sheets breaking down the data for quick reference. So for example Name, Member #, Location, Phone, Item A, Item B, Item C, Item D etc... I need to be able to have all the Members that are in certain cities displayed in it's own worksheet. I have played with the aggregate function, if and functions,... and I am dying. This is for a motorcycle club to keep track of who has ordered what, how much they owe, how much they've spent etc.

View 1 Replies View Related

VBA Macro To Loop Text To Columns Through All Rows Of Data?

Apr 17, 2014

I am trying to get a macro to run in excel that takes a simple text to columns command in one line of data and runs the command on a loop through however many rows of data there happen to be.

I've attached two screenshots - one with what I've got now (Before.jpg) and what I'd like to have after the macro runs (After.jpg). The code below is what I used to get the first text to column breakout, which I can hopefully run on a loop to breakout anything in the DEPT column that contains a "/". It can ignore the rows that only have one department to begin with.

Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Eventually I'll want to create another macro that transposes the breakout text back into the DEPT column and populates it with the corresponding data, but I figure I'll take things one step at a time.

Attached Images
Before.jpg‎
ter.jpg‎

View 10 Replies View Related

Loop Or Macro To Retrieve Rows Of Data From Other Worksheet

Dec 15, 2008

I need to query a master spreadsheet and pull out all the rows on it that have the same name as I have in cell A1 of my spreadsheet

e.g.

Myworkbook, sheetname = queries
Cell A1 = "ABC"

The Macro queries the "test Data" workbook (c:myfolder estdata.xls) "sheet x" and looks in column A for ABC, if it finds ABC it will copy the row and paste it into Myworkbook, sheetname = queries. There are often several rows of data that need to be copied that all have "ABC" in column A.

Also, in column B of the test data workbook, is a number, the highest value denotes the "version" of the data, the highest number is always the latest version of data that should be retrieved, e.g. if 9 the highest value in column B then all data that has ABC in column A and has 9 in column B should be retrieved, all other records should be ignored.

I guess this is a sort of a macro loop but not too sure how to do it.

View 10 Replies View Related

Find Data And Delete Remaining Rows (For Each Loop)

Apr 24, 2009

In the code below I find rows containing "$$ #" at the far left and process it.
For all the remaining rows I want to delete the entire row, my macro only deletes some rows. Obviously I am not grasping something about looping throught the rows to delete unwanted data.

View 2 Replies View Related

Loop Not Working - Move Onto Next Row

Mar 28, 2012

What i am trying to do:

*i have about 2000 rows.
*start from first row
*for each row that contains .pdf in one cell, i want another cell in that row to equal .dir
*hyperlink cell if cell contains dir
*move onto next cell (for all approx 2000 cells)

The problem:

*it is hyperlinking every cell even if the other cell doesn't contain .pdf.
*i think it is looking at the first row and if the cell has .pdf it applies it too all cells

What I want to do:

*select entire row of first row (21)
*if col 21 = .pdf then col 23 = .dir
*of col 23 = dir hyperlink .dir to fname value
*move onto next row (22)....do the same....and so on and so on all the way to row 2039

The code I am using:

Private Sub UserForm_activate()
'Unprotect Workbook
ThisWorkbook.Worksheets("Cover Page").Unprotect Password:="password"
ThisWorkbook.Worksheets("D - Documentation").Unprotect Password:="password"
ThisWorkbook.Worksheets("E - Electrical").Unprotect Password:="password"
ThisWorkbook.Worksheets("F - Process Flow & P&ID").Unprotect Password:="password"

[Code] .......

View 1 Replies View Related

Store Row Number Within Loop And Delete All Stored Rows After Loop?

Sep 11, 2013

I have working code that returns a row number within a for loop based on parameters I set.

Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.

Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*

Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.

All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.

View 4 Replies View Related

Loop Through Rows To Calculate Median For Several Dynamic Ranges With Numerical Data

Jul 6, 2006

loop that can calculate the median of dynamic ranges. I need to run through all columns and for each column find the the ranges (there are more ranges and they are seperated with a blank row) with numerical data and then calculate the median and thereafter continue down to find the next range with numerical data in that row and so on.

View 4 Replies View Related

Recursively & Conditionally Move/Loop Through Folder Hierarchy To Fill Array

May 7, 2008

i am use dir to put into an array all the folders in a folder then enter the first folder and repeat. the problem i am having is when it encounters a file it also puts it into the array and then errors once it trys to enter that "folder" (which is a file)

right now the code doesnt do anything but the plan is then to call another subroutine that lists all the files im looking for in a folder (that sub does work)

what am i doing wrong? all the things i can find on google show it the way i am doing it.

Sub GetDirList(topfolder As String)
Dim FolderArray() As Variant
Dim FolderCount As Integer
Dim FolderName As String

FolderCount = 0
FolderName = Dir(topfolder, vbDirectory)
' Loop until no more folders are found
Do While FolderName <> ""
If Not FolderName = "." Then
If Not FolderName = ".." Then
FolderCount = FolderCount + 1
Redim Preserve FolderArray(1 To FolderCount)
FolderArray(FolderCount) = FolderName
End If
End If

View 3 Replies View Related

Delete Rows Matching Criteria & Move Rows

Aug 28, 2007

I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:

1- all rows with the value "TOM" in column C will have to be deleted.

2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).

View 2 Replies View Related

Move Rows Of Data From Several Columns But Not All Columns?

Dec 18, 2013

Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).

View 1 Replies View Related

Find Rows With Commas, Then Move Those Rows To Another Row

Jan 19, 2010

I've got an excel file with over 20,000 rows with Column A being having values of a "Vendor Number" while Column B has a "Vendor Name" Here is an example:

Vendor #Vendor Name
1001ABC Supply
1002Larry, Moe, Curly Inc
1003John Smith
1004Fred, Wilma, Barney LLC

I'd like it to find the rows where the Vendor Name has a comma present, and then put it in another sheet, or in another column like column C and D. If I can't get both the Vendor # and Vendor Name re-inserted someplace, that is OK. The Vendor Name is the big one.

View 2 Replies View Related

Add Numbers From 2 Rows,then Move To Next 2 Rows & Repeat

May 24, 2007

I have 3 columns and 200 rows. The first column is for Name of product. 2nd column for number and 3rd for $value of number of products. Have 200 rows. Have done a sort and checked. 2 sets of rows are for the same product name. I want to know if it is possible to add up values in 2nd and 3rd column, 2 rows at a time , then 4th and 5th column and so forth, by some formula rather than doing it manually?

View 4 Replies View Related







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