Finding The End Of Rows And Columns And Taking Union Of Sets

Jun 13, 2013

I have a code in software that exports results to excel. But it stores the numbers associated with indices as text. I need to change all of them to numbers. So I have written the following code, that search through the folder, converts text to numbers and colses the file.

Sub ConvertText2NumberFiles()
Dim v As Variant
Dim rng1 As Range, bk As Workbook
Dim i As Long
ChDrive "C"
ChDir "C: ...analysis"

[Code]....

The problem is it takes time becuase it is going through A LOT OF cells. I only need at most the first 4 columns and the first 2 rows. So I wanted to use the union. I wrote the following:

Set IndexColumns = bk.ActiveSheet.Range(Columns("A") & ":" & Columns("A").SpecialCells(xlLastCell))
Set IndexRows = bk.ActiveSheet.Range(Rows("1") & ":" & Rows("1").SpecialCells(xlLastCell))
Application.Union(Range("IndexColumns"), Range("IndexRows")).Select

Which doesn't work! Morover it is only for one column and one row.

NOTE1 : Like any matrix, the intersection of index columns and index rows is blank!
NOTE2 : The lenght of indices is different from one file to another

View 2 Replies


ADVERTISEMENT

Finding Matches Between Two Sets Of Data - Each Set Has Three Columns Of Data

Jul 13, 2013

I am working on large sets of data (more than 50,000 rows of data). I have two sets of data. Set 1 and Set 2 (master data) on the same worksheet. Both the sets of data have three columns each. I am using EXCEL 2007. I was able to accomplish step 1 below.. but I am totally lost with step 2 since i have an additional criteria for the "year".

I have attached the excel sheet as well. This is what I am trying to accomplish:

1) I want to find exact matches in set 1 and set 2 and highlight it or do something to show that a match was found. The challenge is the data in set 1 can occur anywhere in set 2.

2) Add to the complication .. my criteria for matching the year is different. If the Set 1 "year" is equal to or greater by 1 yr or greater by 2 yr when compared to Set 2 "year", I want to treat it as a "match".

For example, from the data attached:

Set 1 data in row 4 is: ATLANTIC ABSECON 2004
Set 2 data in row 3 is: ATLANTIC ABSECON 2003

I want to treat these two data as "MATCH" since ATLANTIC matches ATLANTIC, ABSECON matches ABSECON and according to one of my criteria for year, Set 1 "year" is greater by 1 yr than the Set 2 "year".

View 1 Replies View Related

Finding Sets Code Slow To Run

Aug 23, 2006

If you open the file sets.xls, you will see that I have listed 5 sets of 10 numbers.
A21:J21
A22:J22
A23:J23
A24:J24
A25:J25

These sets are such that all 10 numbers matches with sets of 20 numbers listed vertically in the range K1:S20 atleast twice (T21:T25 -twice)

I know there are 50+ sets like these and I have listed just 5 of them.

How can I search the other sets?

I have uploaded a zip file which has an excel file. Run the module "DoIt" and you will get all 50+ sets like these.

The problem is that the module takes 42 minutes on my Celeron 800 Mhz 256 MB SDRAM. I want to reduce this time.

View 4 Replies View Related

Loop Through Rows Of A Union Range

Aug 19, 2006

'How do I use a FOR NEXT loop on a union to just list all cell values in Col A?

'Just need a little help getting a handle on ranges and the union of ranges.
' This is my first time posting a question so any patience is appreciated.

Dim r1 As Range
Dim r2 As Range
Dim myMultipleRange As Range
Dim xCell As Range

Set r1 = Sheets("Sheet1").Range("A1", Range("A65536").End(xlUp))
Set r2 = Sheets("Sheet1").Range("B1", Range("B65536").End(xlUp))
Set myMultipleRange = Union(r1, r2)

r1.Select

For Each xCell In r1
'Ive been using this type of code to access anything I need from a sheet.
Debug.Print r1(xCell.Row, 1), r1(xCell.Row, 2), r1(xCell.Row, 3)
Next xCell...............

View 9 Replies View Related

Excel 2007 :: Union Of Values In 2 Columns To One In Increasing Order?

Jul 20, 2012

I have to find the union of 2 columns in excel and club those 2 columns into a single column with values in the increasing order.eg: column 1- 0 2 4.. , column 2- 1,3,5.. final result in column 3 should be 0 1 2 3 4 5...plz let me know the code for this that i can run in VB editor(- excel 2007)

View 9 Replies View Related

Finding Numbers Of Rows And Columns?

May 5, 2013

How I can know the numbers of rows and columns in a excel sheet?

View 5 Replies View Related

Finding Frozen Rows (and / Or Columns)

Aug 12, 2014

Given that a worksheet has frozen rows and/or columns, how do you programmatically determine what they are?

There are plenty of examples around that tell you how to create frozen rows/columns by setting ActiveWindow.FreezePanes = True but, given this is only a Boolean value, reading it does not tell me where the freeze is.

View 2 Replies View Related

Finding Duplicate Rows Based On Values In Multiple Columns?

Mar 28, 2014

I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. I basically want to Group based on 4 fields and find the average of the 5th field.

My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.

View 9 Replies View Related

Finding Macro That Will Combine / Consolidate Rows When Cells From 2 Separate Columns Match

Sep 26, 2013

I need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...

beginning:

Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100

This is what I need the macro to do:

Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100

View 2 Replies View Related

Taking Entries From Certain Columns And Putting Them Together

Apr 4, 2014

I have a huge spreadsheet where after every 12 columns there are two "special" ones, which contain certain entries. In the first of these two, the entries from the second one are numbered in a certain way. Please see the example.

I need a formula in a separate column (yellow) which will gather all these entries from all columns, and put them in order, as I did manually in this example.

Instead of 4 "groups" of columns, in my real file I have 200 of them, with 400 rows each, which makes it more complicated..

View 2 Replies View Related

Taking Content Of Columns And Returning In New Sheets

Sep 9, 2013

I've got the following scenario:A sheet (Sheet1) with some columns that look like this:Billy

Bobby
Sally
Joe

C
D
C
D

[Code] ........

I have a second sheet (Sheet 2) that looks like this:

Totals
Billy
Bobby
Combined
Billy Score
Bobby Score
Billy Cumul Score
Bobby Cumul Score

-blank-
C
C
CC
3
3
3
3

[Code] .......

Columns 4 through 8 are automatically populated via formulas I've created, after Columns 2 and 3 are filled. What I would like is a macro that does the following:

Copy the data from the first column ('Billy') in Sheet 1 and put it into the second column of Sheet 2Copy the data from the second column in Sheet 1 ('Bobby') and put it into the third column of SheetNow my formulas will populate the other columns.Take these "results", all of the info currently in Sheet 2, and then paste (values only) them into a new sheet called "[Sheet1.B1] x [Sheet1.C1]" in this example, it would be "Billy x Bobby" 4. Repeat with every combination of non-duplicated column match-ups (every column gets matched with every column once, and once only):

a. Billy x Bobbyb. Billy x Sallyc. Billy x Joed. Bobby x Sallye. Bobby x Joef. Sally x Joe

So, in the end there would be a workbook with 8 sheets.

View 1 Replies View Related

Taking Sum Of Column When Some Rows Hidden?

Feb 29, 2012

I have autofiltered a column to meet a certain criteria (which hides some of the rows), then I want to put a cell with the sum at the bottom of that column. When I do this, it takes the sum of all of the rows in that column, even the rows that are hidden. Is there a way to only take the sum of the rows showing?

View 5 Replies View Related

Compare 2 Sets Of Data Each With 2 Columns?

Nov 10, 2013

I have 2 sets of data/array/range. Each set consists of 2 columns with a large number of rows.

I want to compare my 2nd data to a master data. And list if anything is different in 2nd set of data from master set in column A than highlight the difference or copy the value to another place.

Also want to compare the 2nd column if column A was same and consider both column A and column B for that associated row different if column B is different.

The trouble for me comes in because the list is never alphabetical (sort doesnt work cause of funky naming) and never of the same size.

Attached is a photo of an example for maybe an clearer understanding. Also attached an example excel sheet I tried it within excel but cant seem to figure out how to look also for the 2nd column, so im trying to avoid the within excel route and go using vba ...

T3OCcxw.jpg
example.xlsx

I attempted it with a very basic code thats not working =/ just cant seem to figure how to code to get the desired result

VB:
Sub matchdiff()
Dim cell As Range
Dim found As Range

[Code].....

View 1 Replies View Related

Subtotal Sets Of Rows

Feb 10, 2007

i have an excel sheet with columns including city & order value. i want to group rows with the same city name, and then subtotal the order values for each city. how do i do this?

View 10 Replies View Related

Hide/Unhide Multiple Sets Of Columns With Toggle Button

Sep 23, 2009

I am trying to create a very large spreadsheet and i have got everything organized, but for easy viewing i want to have a toggle button that hides and un-hides certain sets of the columns (ie. Press down and it hides columns "B-F", "H-J", and "M-O".... then on depress it shows all the information again)

I have got some of the coding down, but am only able to get it to hide one set at a time, not all the sets. Can anyone help me out and let me know how i tell it to run multiple commands from the one press? here is the code i have already: ...

View 6 Replies View Related

SUMIF - Trim And Concatenate 2 Sets Of 2 Columns Giving Answer In Another Column

Jun 22, 2008

I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.

I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…

A = 2000 text
B = 1234 text

F = 2000 trailing spaces, text
G = 1234 trailing spaces, text

I = 10,000

Answer in column J

I want 20001234 = 20001234 = 10,000 for the whole column.

I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.

View 9 Replies View Related

Adding A Constant To Sets Of Rows Of Variable Size

Jul 30, 2009

I have data from 100 trials. Each trial has a variable number of data points (rows), but each row is labeled in the first column with the trial number. I would like a macro to identify the first row of each trial, calculate the difference of the value of the cell in column G of that row from a constant (718), and then add that difference to that cell and all other cells in that column of that trial.

So based on the attached sample, I would like all values in column G for trial 2 to be +1, for trial 3 to be -5, and for trial 4 to be +2.

In the file, I've cleared all the data from the impertinent columns. Eventually, I will also want to perform a similar process on column H but with a different constant.

View 2 Replies View Related

VLOOKUP In Large Data Sets Of More Than 16384 Rows

Mar 27, 2006

I have a problem with VLOOKUP looking in sorted lists of more than 16384 items.
Basically it fails at 16385th item

I have developed a VLOOKUP function that looks up in multiple tabs in
multiple files and returns a value. However I have discovered that it fails
at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
anyone else encounteed this problem? is it just my machine, is there a
solution / bug fix?

If I use FALSE when the item to be found is beyond 16384 the function
returns the 16384th value this is a warning to other users who may not have
noticed as I did not initially.
--

View 12 Replies View Related

Copy Data Sets Seperated By Blanks Rows

May 8, 2008

I have an excel file which contains 2 set of data. 1 with 14 rows and other with 20 rows. These are randomly distributed in the sheet and are abut 1000 of data set in a sheet with a combination of 14 and 20 row data set. Data is separated by blank rows, i.e. if it is 14 row data set it would have a blank after 14th row and similarly a blank after 20th row for a 20 row data set. Following the blank another data set starts which might be of 14 or 20 again. I have attached a sample sheet for reference.

View 5 Replies View Related

Macro To Break Out Sets Of 6 Digits Numbers In One Cell Into Their Own Rows

Jun 1, 2012

vb code that will break out each 6 digit media used in their own rows such as in the illustration below.

Sample data before macro
Master Backup
GTI - Hostname

[Code]....

View 2 Replies View Related

To Insert Down Between Sets Then Border Sets

Apr 10, 2008

Below is a series of sets. Column A is the set number. I need a macro that will insert a row between sets and then put a border around each set. In my spreadsheet the sets are from A1:C500. Sometimes the sets are only 1 row, sometimes multiple rows. It looks like I will be doing one of these sheets every week. So far I have been doing it manually, but a macro sure would save some time.

View 13 Replies View Related

Finding Rows With Specific Characters - Copy Only Those Rows

Apr 7, 2012

I'd like to be able to search a spreadsheet for a specific word or series of characters. Once all of the ROWS that contain the characters are found, I'd like to be able to have another spreadsheet with ONLY those rows (all columns).

View 8 Replies View Related

Add To Selection By Right Clicking And Union

Jul 15, 2014

I'm trying to make a code so that when optionbutton1 is checked, it allows you to right click to select a cell, then right click on a different cell and select that cell as well as the previous cell, etc. Here's What I have so far.

[Code] .....

View 6 Replies View Related

With For Multiple Ranges (not Using Union)

Dec 29, 2008

Quick question: When using "With" is it possible to specify 2 ranges, or will I need to do 2 different with blocks? The reason I say not using Union is because I need my ranges seperate. I run 1 set of commands on Range 1, and 1 set of commands on both Range 1 and 2.

View 3 Replies View Related

Union Named Ranges

Apr 29, 2009

(Using Office 2003 on XP Pro) I have two named ranges that I want to union into one big named range so that I can use the big named range in a validation table. Unfortunately the big range does not appear on the list of named range so I switched to VBA to try and lick this but really I was hoping a non-VBA solution exists. Exploring possible VBA solutions, here is what I have so far:

View 6 Replies View Related

Union Ranges On Two Different Sheets

Mar 3, 2008

I'm trying to union two different ranges that exist on two different sheets and then copy the unique values into a third range with just the unique values. I was going to then use the unique range as my rowsource in a listbox. This is the code I was working with so far but i'm getting a compile error (method range of object failure).

Private Sub UserForm_Initialize()
Dim range4 As Range
Dim range3 As Range
Dim range2 As Range
Dim range1 As Range
Set range1 = Worksheets(1).Range("MyRange")
Set range2 = Worksheets(2).Range("MyRange2")
Application.Union(Range("range1"), Range("range2")).Select
Selection = range3
range3.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=range4, Unique:=True
Me.lstone.RowSource = range4
End Sub

View 9 Replies View Related

Selecting Ranges On Different Sheet Using Union

Nov 7, 2013

I am writing a UDF to find the average of two ranges, which start at the StartDate and then go back a certain number of days, that are on another sheet. When I use Union to try and unite my two ranges the resulting range just equals the first range.

VB:
Public Function ModifiedAverage(StartDate As Variant, SheetTenor As String, History As Double)
Dim DateRow, NbCols As Double
DateRow = Application.WorksheetFunction.Match(StartDate, Sheets(SheetTenor).Range("A:A"), 0)
NbCols = Application.WorksheetFunction.CountA(Sheets(SheetTenor).Range("6:6"))

[Code] ....

View 1 Replies View Related

Union Result Does Not Include Second Range?

Aug 15, 2014

I'm writing a function which includes the need to merge 2 ranges into 1 new Range object. The problem is that adding a breakpoint just after this occurs, and viewing the immediates, the new range only actually contains the first input range

For testing, and displaying here I've placed the 2 sub-Ranges into temp objects

[Code]....

The breakpoint is on the next line (not shown), so I can view all the Locals

tempyR1 and tempyR2 both are Range objects containing the correct Range data as expected from the code
However, objExcelRange only contains exactly the same as tempyR2

I'm using the Locals to check as this new combined Range will undergo further processing before being placed onto a sheet, and the combined Range will actually be generated dynamically depending on what options the user picks from a second sheet within the WorkBook - which could in fact be created from between 2 and 5 sub-Ranges

View 7 Replies View Related

Trying To UNION Same Cell And Blank Cells Possible?

Nov 12, 2012

I'm trying to select the same cell and a blank cell multiple with a UNION(). Is this possible?

Here's a summary of my code:

Code:
Dim c
Dim Rng As Range
Dim blankC As Range

Set blankC = Range("XFD1048576")

Code] ......

Ultimately, I'm trying to copy various cells and reorganize them including blank cells to conveniently use UNION for a quick copy and paste as opposed to explicitly defining where to paste everything.

View 9 Replies View Related

Checking If Selected Cells Is A Union

Aug 24, 2013

I would like to check if selected cells is a union of several ranges or one solid range. I am sure there is a way, but did not manage to find it. How to do it?

View 2 Replies View Related







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