Macro Logic: Extract Higher/lower Values Based On Unique Cell

Jun 12, 2009

This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):

1) I want user to input starting cell. So I need to create a button for that.

2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.

If the value is greater than original cell then logic needs 2 outcomes:

a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.

View 2 Replies


ADVERTISEMENT

Speed Improvement - Macro Extract Higher/lower Values And Note Time Differences

Jul 8, 2009

I have a macro that needs a major improvement in performance. I have a Quad Core 2.67GHz single processor computer and with the current logic it calculates 20 values per minute. I have data sets that can be up to 400,000 data points, which means it will take 333 hours. The attached Sample file has over 9000 point. For this data set it takes 7.5 hours.

The core logic of the macro is to extract what I call Break Point into column M and Time Difference between the Break Points to column O.

I got the code from this forum (thank you DonkeyOte) and made some modifications. The modified logic does the following:

1) The user inputs the starting cell. In the Sample I use G200.

2) From cell defined in button, It moves down one cell in that column and compares that value to the original cell.

If the value is greater than original cell then there are 2 possible outcomes:

a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value to the column M (Break Point value) and copy the Time Difference Value to column O. Time Difference is calculated as the difference between the 2 point breaks in Column A. In this case Ive hardcoded 0.003472222 to get 5 minutes which matches up with each incremental time in column A, but I would like the Macro to automatically calculate that by the difference between Column A values.
b) move down 1 cell in column and if that value is greater than original cell, then move down again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value to column M and copy the Time Difference Value to column O.

Once lesser value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.

At any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column G.

OR
Here's the logic now in reverse I need:

If after moving down 1 cell of original cell, the value is less than original cell then logic has the following 2 outcomes:

a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O..
b) move down 1 cell in column and if that value less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.

Once greater value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value lesser than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.

Again, at any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column.

Once all the Break Points and Time Difference between Break Points are extracted for each value in column G, the AVERAGE, STDEV and MAX values of column O are calculated in column Q, R and S.

There is a loop that controls the execution of the core logic until the last value in column G. In the macro Ive hardcoded last row being 9171, but Id like the Macro to figure out what the last row is automatically.

I know this description is a bit long but Ive worked for many hours on getting it work properly. I just need some help to make it run much faster. I hope someone can help me out on this. I have the right logic, just need the speed now and I really cant figure it out.

Attached is a sample files demonstrating showing how the logic to flips back and forth as moving down the column starting in cell G200.

The sample shows the results first 4 loops (Rows 200 to 203). The values in M and O are the results of the fourth loop.

Again, assistance is very much appreciated because I've taken this as far as I can with my limited experience.

EDIT - I can embed the Macro, but it'll probably much easier to actually see it in the sample file.

View 14 Replies View Related

Formula: Higher, Lower Or The Same To A Specific Cell Value

Aug 24, 2009

I have a spreadsheet that I am using to track competitor retail pricing
The problem is that I have a list of values and I want to be able to show how many of these values are either higher, lower or the same to a specific cell value. However one complexity that I have is that some of the competitors do not have values for all records (i.e. some are blank) and I want to discount these from the counting.

View 12 Replies View Related

Compare Two Cells - Populate Higher In One Color And Lower In Another

Feb 12, 2013

I would like to compare two cells side by side and populate the higher of the two cells in one color and the lower in another

View 3 Replies View Related

Determine Values In Cells: Get The Lower Values Between To Cells And Have The Lower Valued Cell Highlighted

Oct 27, 2009

I M trying to get the lower values between to cells and have the lower valued cell highlighted,,,i have over 43 thousand lines of data to go throughand i was wondering if there was a quicker way to do this,,,for example cellA1 is $4.25 and cellA2 is $5.25 i want cell A1 to be highlighted,,is there a way?

View 2 Replies View Related

Extract Specific Unique Values From Row Into Cell

Feb 27, 2014

I've had a look at some examples of extracting unique values but it seems quite complex to me!

I have a list of customer names in column A, column B contains a material code (number format), column C contains the material information sheet (text & number format), column D contains the date that the material information sheet was sent (all column headers are in row 1).

Columns E, F & G follow the same format as above but for a different material code - this is repeated further in Columns H, I & J.

All of the material codes are different, but some material information sheets can be the same for some materials.

In column K I need to identify the unique material information sheet values for each customer, as some are duplicated.

Is this possible? There may be some blank values in the row i.e. a customer does not receive a certain material.

Perhaps just an easier way of organising the spreadsheet!

View 9 Replies View Related

Filter A Table With An OR Logic Based On Columns Values

Feb 10, 2010

i have a table and have some columns.

i want to filter the table so that i just see the records which have "text1" in ther column "F" OR they have "text2" in ther column "H", for example.

How can i implement an OR filter ?

View 2 Replies View Related

Macro That Merges Duplicate Rows Based On Unique Values

Jan 27, 2014

I have a spreadsheet that lists employees and their certifications. If an employee has multiple, then they will show up on as many rows as they have certifications.

The macro I have merges them into one row with a line break, but only the first column's unique value has been merged while the other columns containing their own unique values are duplicated when I want them to show up only once. Example: Jane Doe shows up 2 times on the report. Her name should only show up once on the row, not 2 times with a line break.

Here is the code. I have also attached an example of what I need. Because the attachment is a simpler version of the actual report, is it possible to specify which rows have the unique values and which ones don't?

View 2 Replies View Related

How To Extract Unique Values

Aug 27, 2013

I am trying to extract unique values from a column but i can't, i am using excel 2010 and i have try the formula: =INDEX(List;MATCH(0;COUNTIF(C1:$C$1;List)+(COUNTIF(List;List)<>1);0)) but nothing.

Example:

NAMES UNIQUE VALUES
ΑΒΑΚΟΒΑ ΛΑΡΙΣΑ ΑΒΡΑΜΟΠΟΥΛΟΣ ΗΡΑΚΛΗΣ
ΑΒΑΚΟΒΑ ΛΑΡΙΣΑ
ΑΒΟΥΡΗΣ ΔΙΟΝΥΣΙΟΣ
ΑΒΟΥΡΗΣ ΔΙΟΝΥΣΙΟΣ
ΑΒΡΑΜΟΠΟΥΛΟΣ ΗΡΑΚΛΗΣ
ΑΓΑΘΟΣ ΕΥΑΓΓΕΛΟΣ
ΑΓΑΘΟΣ ΕΥΑΓΓΕΛΟΣ

View 11 Replies View Related

Extract Unique Values Only

Feb 18, 2006

Given a long column of values, how do I extract only unique values? Small example > Values of 1, 2, 2, 3, 4, 4, 4, 5 in column A. Would like to extract unique values ... 1, 2, 3, 4, 5 ... and put them in another column.

View 13 Replies View Related

Extract Unique Values From Row

Dec 23, 2009

I Want To Extract One Value From The Row Skipp Repeated
In Other Word Need Formula For Row Instead Of Advanced Filter, Unique

Sheet2 ABCDEFGHIJKLMNOPQRSTUVWX1Data123451234554512234511882Result123458 Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Extract Unique Values Greater Than Zero?

Oct 24, 2012

How can I extract the unique values from the original list but only those whose their SUMIFS is greater than zero? in my example "Blue" , "Yellow" & "Red" should not be extracted.

Sheet1  ABCDE1Original Unique2Purple10 Purple103Blue0 Blue04Pink1 Pink15Yellow0 Yellow06Green13 Green31
7Blue0 Red08Green18 Black29Yellow0   10Red0   11Black2   12Pink0   13Black15   14Purple6   15Red0   
Spreadsheet FormulasCellFormulaD2{
=IFERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$15),0)),"")}E2
=IF(D2<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D2),"")D3{
=IFERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$15),0)),"")}E3

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

View 9 Replies View Related

Extract Unique Values Of 2 Sheets

Nov 3, 2013

Extracing unique values based on two lists(2 sheets).

My scenario:
Sheet1 has 2 columns with one million lines. (Master sheet)Sheet1 ColumnA=serial numbersSheet2 has 200,000 lines with same format. columnA= serialsheet2 has some new serials and some existing serials
Serial is 18 digit number saved as text (dunno of that matters)

Solution:
how can I get vba to check serial agaisnt serial ( Sheet2 ColumnA against Sheet1 column A)extract all new serials (unique of sheet 2 only)unique lines to export to new sheet or replace sheet2

View 5 Replies View Related

Trying To Extract Unique Values With A Formula

May 14, 2014

I have a very large table in Sheet 1, in the following range A10:1:3068.

Column C has the company name, and there are multiple instances of the same company name in this column.

(Columns E to I are part of a pivot table and I have various formulas in A thru D).

In column A I have a value indicating whether the company has 'exposure' (an internal meaning for our sales team). If the company has exposure, then the word exposure is indicated otherwise the cell is blank.

What I need to do is in Sheet 2, create a table that extracts a unique list of company names from column C only where it has the word 'Exposure' next to it in column A.

I would like this to be dynamic so if I change a value in the pivot table in sheet 1 the values in the table in sheet 2 will update accordingly.

I am figuring this is an index and match or perhaps a countifs.

View 9 Replies View Related

Extract Unique Values In A With Zeros In B

Jan 24, 2009

I want to extract all those unique values in column A, if and only if, all their repeated values in A
have zeros in B. I mean, if one value appears N times in column A, and for the N repetitions its
respective value in B is zero, this value in A must be extracted from the list.

I was trying with count frequency of values, but I dont know how to extract
unique value with the required condition.

Example:
*ABCDEFGH1********2XYA-121******3XYA-120******4VVT_320******5VVT_320******6VVT_320******7RRT0******8UBN-550******9UBN-550******10PPT_431******11********12********13*Extracted values have all their repetitions with 0 in column B**14VVT_320Value extracted because appears 3 times, and those 3 times its value in B is 015RRT0Value extracted because appears 1 time, and that 1 time its value in B is 016UBN-550Value extracted because appears 2 times, and those 2 times its value in B is 0 Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Extract Unique Values Between 2 Worksheets

Sep 21, 2007

I have 2 sheets in a workbook. Column A on sheet 1 has numbers such as 123456, 123457, 123458 ect ect. Column A on sheet 2 has same amount of numbers but mostly different. However there are a few duplicates. QUESTION: How can i write a code that will fit in a new sheet lets say sheet 3 that will pull out only the list of numbers from sheet 1 and sheet 2 that are NOT duplicates?

View 5 Replies View Related

Extract Unique Values From Range

Jun 16, 2008

I have data of different accident year. Now I need to identify different Accident Year and use them in Macro to do certain things. For example in the file attached I am using them in Msgbox. Now to acheive that I first extracted unique values using advanced filter on Column C and later use the different value to POP up the Msgbox. (This is achieved). Now I want to skip this part of doing Advance filter to extract unique value. Is there a way I can know unique value of data through the code itself and use them in program. here is what I am doing currently: I have delted the most of the data cause I am concerned with coulumn containing acc_year only.

Sub test()
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= Range("C2" _
), Unique:=True
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C3").Select
Do While ActiveCell.Value <> ""
MsgBox ("The Year is " & ActiveCell.Value)
a = ActiveCell.Row
Range("C" & a + 1).Select
Loop
End Sub

View 3 Replies View Related

Copying Formula To 8 Cells Lower With Result Of 1 Cell Lower?

Sep 30, 2013

create a formula copying a formula from cell a5 to a20 but i want the result to be 1 cell lower from a5's result

View 6 Replies View Related

Extract Unique Values From Multi-Columns

Feb 20, 2013

I wan to extract Unique values, here is the data for reference, I have made to tables the second table is desire result I wanted.

Sheet30  ABCDEFG1JOB #WASHQTY JOB
#WASHQTY27800A1000 7800A100037801B1000 7801B100047802A1000 7802A100057800
A1000 7802C100067802C1000 7805B100077805B1000 7807D100087800A1000 7809D
100097807D1000    107802C1000    117809D1000

View 9 Replies View Related

Extract Unique Values Which Partially Match?

Jun 20, 2014

I can extract unique values from the list but thing is I need extracts only those values which match partially below is scenario

Column A has values ----
Column B has Countries..

so I need extract only those values which eg : Contry is "*Ind*"

Code:
=INDEX($A$2:$A$15,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$15),0))

above formula I used to get unique values I need to add one more criteria which gives only matched.

View 1 Replies View Related

Extract Unique Records Based On 1 Column

Aug 27, 2007

I have a single excel data sheet with 10 rows of header information and then multiple rows and columns of data

I need to extract the 10 rows of header data plus the rows for each unique record in Col A into its own separate worksheet, with the work sheet name being the unique record from Col A

To further add to the challenge, the data in col A may have "/" in so will not comply with excel sheet naming convention so would like an error message to remind me to manually change a sheet name.

I attach an dummy data sheet just to show what I mean!

View 6 Replies View Related

VBA Macro To Extract All Unique Rows

Oct 28, 2013

I have the following table A

1
2

B
1
1

B
2
1

[Code] .....

I would like to extract all the unique rows from the above table so the outcome will be

A
1
2

B
1
1

[Code] .....

However I can't think of a simple macro to do it.

View 2 Replies View Related

Compare Data In 2 Columns Extract Unique Values And Paste Into 3rd Column?

Sep 11, 2013

Here's how my daily report is currently set up:

ExternalInternalDifferences
123000123000
234564234564
345456345456
456567378987
565456456567
654547524564
788879565456
865478654547
745654
788879
865478

The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:

ExternalInternalDifferences
123000123000378987
234564234564524564
345456345456745654
456567456567
565456565456
654547654547
788879788879
865478865478

While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.

The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).

View 8 Replies View Related

Extract Unique Values From Column B Between Date Range Textbox1 And Textbox2

Jul 3, 2007

Sheet1 has following two columns

----A----------B
01-07-2007---10
01-07-2007---20
02-07-2007---30
02-07-2007---40
02-07-2007---40
03-07-2007---60
03-07-2007---70
03-07-2007---70
03-07-2007---90
04-07-2007---100
04-07-2007---110

Sheet2 has following three cotrols

Textbox1=02-07-2007
Textbox2=03-07-2007
combobox=?

I want to Extract Unique values from column B between date range textbox1 and textbox2 and finally how to add these unique records in combox1

View 3 Replies View Related

Cell Based On Lower Upper Case

Oct 20, 2009

I've got a column of data that I need to split into further columns, the only way of identifying the split from the data is if a lower case character appears next to an upper case character.

For example, a cell in column A could look like;

Computer ProductsDrivesDVDExternal
And I'd need to split that out to cover columns A B and C with

Computer Products / Drives / DVDExternal
I'd need to do that for all the cells in column A.

View 9 Replies View Related

Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3
All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

View 2 Replies View Related

Macro Loop To Extract Specific Values And Min/Max Values From Column/Rows Range

Jun 3, 2009

Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......

View 13 Replies View Related

Macro To Find A Cell Containing Text /select Range Offset / Cut And Paste To Lower Rows?

Feb 4, 2014

I am trying to find a macro that can search a sheet for any cell that contains the text "Not on AOI" selects a range that contains that cell, 81 rows below, and 2000 columns to the right, then cuts the selection and pastes it 162 rows below the original cell where the text was found. What's hard is that the number of columns between the "Not on AOI" cells is variable.

I'm very new to excel macros and the parts I think I've put together are:

Cells.Find("Not on AOI", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True).Activate

[Code].....

View 1 Replies View Related

Find Two Closest Values That Are Higher

Jun 30, 2009

Got an equation nightmare on my hands.
So i have 3 columns of data
A: State Abbreviate
B: Benchmark Number
C: New Number

I am trying to get an equation that in column D will return the 2 .....

View 11 Replies View Related

VBA - Hide / Show Values Of 0 Or Higher

Jul 1, 2013

Basically, I am looking for a macro code to link to an 'Update' button that will do the following;

I will have 2 sheets, one sheet will be referencing data from other sheet with the following

=IF(Log Sheet!A4="Yes",(A1),"0")

This means, if it needs to be 'referred' it will go to the other sheet, and if it doesn't, then it wont and it will show up as 0.

Basically, every row will have this type of formula for 30 columns, and i would like a macro code that will hide values of 0, but even once hidden, will then show them again if in the other spreadsheet i change the value to yes, meaning i want the previously hidden row to show.

View 1 Replies View Related







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