Filtering Out A Number Of IDs From A Larger ID Pool

May 1, 2009

I have Excel file A with a full list of IDs that were selected to take part in a study - about 5000 IDs with only one field "ID". I also have Excel file B with about 1500 ID of people from file A BUT who already took part in the study. Now what I need to do is filter out from file A all the people in file B so in file A I am only left with those who didn't take part.

View 2 Replies


ADVERTISEMENT

Sort A Pool Standing

Apr 3, 2007

I'm running a pool and I would like to sort all the players by the total of points.

It is hard to explain with out showing the spread sheet

View 9 Replies View Related

Hockey Pool Formula

Apr 21, 2006

This might be a little long/difficult to explain so I have attached the excel sheet for viewing.

I am trying to adapt cell I11, which now equals cell B10 or B12 (depending on where you type in the "x"). I am trying to formulate it to be either cell B10, B12, B18 or B22, I want it to read the highest seed number (5, 6 or & 7) again depending on where you place the "x". Let me know if I have left anything out.

View 8 Replies View Related

Filtering By Date And By Number?

Apr 23, 2012

I have a spreadsheet with 5000+ rows. On one column there is a user number, the user number repeats few times. Therefore, there is a different number of rows for each user.

Also, the first line for each user has a date on a different column.

I am interested in filtering out the users who do not match a certain date range. The issue is that the code I found hides all of the rows without dates and also the rows that are with the range of dates.

View 2 Replies View Related

Filtering Data By Number?

Feb 6, 2014

I have a table with numbers in a column and various other data. I use this data to track over 30 and want to filter everything thats over 60. Currently I use compound COUNTIF to check 2 criteria using :

=COUNTIF('1SBCT'!F:F,"No*")+COUNTIF('1SBCT'!H2:H3,"")

But would like to an additional criteria in that it will count everything over 60 after with counts the 2 conditions above.

Example: counts how many have a No* or blanks and once those are sorted checks for over 60.

View 8 Replies View Related

Group Of Decimal Coordinates - How To Pool Those Which Are Close One Another

Jul 5, 2014

How to set a formula to calculate distances (by using a function I provided) among all the observations (each one with a latitude and longitude value expressed in decimals) in an excel sheet. Here it is the previous thread.

I slightly modified the solution MarvinP suggested to me by using my own function to calculate distances among coordinates.

Attached you can find the excel with this. You will see that on the right side of the sheet, in yellow background, there is a matrix whose non-blank cells each one reporting a case of close coordinates (e.g.: 4-5 means that observations 4 and 5 are less than 15 Km apart one from another). Cells of the left-side matrix contains the function used to calculate distances among each observation with any other. Matrices are 116X116.

However, I am now jammed because I don't find an easy way to pool the observations found to be close each other (I have used a distance threshold of 15 Km - you can see it in the formula inside each cell of the "yellow" matrix").

The ideal way to establish new coordinates values for observations close one another would be by using coordinates which are at an intermediate position, however, this is not my priority, it would be OK also using one arbitrary value for all of them, i.e. picking up one pair of coordinates of one of the close observations and attribute this to the other. I have seen that, by excluding nonsensical similarities (e.g. 1-1, 2-2, etc.), there are groups of close observations of size 2 (e.g. 4-5) up to 10 (nine observations very close to a given one).

Attached File : CoordinatesPooling.xlsx‎

View 4 Replies View Related

How To Combine Pool Of Excel Files Into One Workbook

Mar 17, 2014

I need to use VBA read all historical data files, then, copy all the data from those files to Sheet[FX Historical Data] and Sheet[Position Data] respectively.

First, I copy a code from the text book, which allows me to copy one data files every time. Now, I have only 6 files need to copy, but if there are thousands of files? I try lots of codes but I still cannot find a way to solve my problem.

Second, can I use the function of "folder dialog open" to select the data files I want, and then copy the data inside to Sheet[FX Historical Data] and Sheet[Position Data]?

View 2 Replies View Related

Displaying Message Box For Top Scores In Football Pool

Dec 6, 2009

I have a football pool I am doing with my family. I would like a macro that displays a message box that tells me the leaders of the pool using the grand total number. So in my attachment, the message box would say something like:

Sue is in first place with 12 points,
Bob and Dave are in second place with 9 points,
Larry is in third place with 3 points

It doesn't need to be exactly like that, but you get the gist of what I am looking for. The catch here is that the grand total row changes each week as I add games in, so the row moves down every week. I need the macro to stay with the grand total row from week to week.

View 4 Replies View Related

Pool League And Use To Track Player Handicaps

Oct 18, 2006

I run a small town pool league and use excell to track player handicaps, ect. we use a simple system for handicapping. Players can be from 2- to 7+ (2- 2 2+ 3- 3 3+ 4- 4 4+) and so on. if a player is a 2+ and wins they go to a 3- and so on. I want to be able to just type a W or an L and have it look up the players previous handicap and return a new handicap in the next field. I've allready figured out how to count the W's and L's and return the total for a teams weekly wins on another page, and give stats on a players wins and losses. I then print each sheet out weekly. Teams have 8 players but only play 5 each week so some players might not play for 2,3 or more weeks. I also use access to keep track of all the people that have played in the league. some players play a few sessions then quit for a few. this way i can keep track of their handicaps when not playing and havew a list of all the people that have played in the league. Can i get excel to transfer a players handicap to access each time it changes?

View 9 Replies View Related

Filtering Highest Revision Number For Individual Products

Apr 16, 2013

What's the easiest way of filtering a list of products to only show the highest revision for each product, preferably without using macros or VBA?

E.g. I only want to show the asterisked rows in the table below:

Product Rev
AAA 1
AAA 2
AAA 3*
BBB 5*
CCC 3
CCC 6*
DDD 1
DDD 4
DDD 7*

Potentially, other (random) data could also exist in the same row.

The example above is sorted alphanumerically but a solution for an unsorted table would be even more useful.

View 7 Replies View Related

Data Filtering Based On Last Two Digits Of Large Number

Mar 17, 2006

I have a very large database where I want to filter out numbers that do
not have certain two digits as their last two. For example, the column
based on which I want to filter out data contains numbers like
197301310153. I want to filter out anything that does not end in 53....

View 13 Replies View Related

Count Unique Instances Of Date While Filtering Based Upon Date And ID Number

Aug 14, 2014

I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.

I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.

I've attached an example work book and I would like the result in H2.

Example Workbook.xlsx‎

View 5 Replies View Related

Exponential Value For Larger Numbers?

Feb 27, 2014

I have the following data

A1 - 65.23
A2 - 1923.52
A3 - 945.18
A4 - 182.54

In column B

B1 =exp(A1)
B4 =exp(A4)

both are fine, however

B2 =exp(A2) - it is showing #NUM
B3 =exp(A3) - it is showing #NUM

Same case for all larger numbers

View 1 Replies View Related

Returning The Value Of The Larger Cell

Feb 5, 2009

I am looking to return the larger value of 2 cells into a seperate cell.

View 2 Replies View Related

How To Increase Value To Larger And Not Lower

Nov 26, 2012

I have 83,33 value. If I like to increase into 84 and not 83 without decimal point

View 11 Replies View Related

Importing A Larger File

Dec 12, 2006

I want to do is import a TXT file that has about 70000 rows of numbers into excel. I know it has a max of 65536 so I would like it to import the numbers and put them into 2 columns C and the rest
in D.

Sub ImportLargeFile()
'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.

Dim strFilePath As String, strFilename As String, strFullPath As String
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object

'Get a text file name
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...") ........................

View 9 Replies View Related

Extracting Name From A Larger String

Jan 23, 2008

I'm trying to extract a first name and surname from a username :

CN=Joe Bloggs/OU=stneots/OU=EU/O=SAC needs to read Joe Bloggs
CN=Fred Smith/OU=stneots/OU=EU/O=SAC needs to read Fred Smith
CN=Ray Jones/OU=stneots/OU=EU/O=SAC needs to read Ray Jones

The string is always in this format and the name always has a space between. Given a day or so I could probably figure it out, but unfortunately deadlines are breathing down my neck,

View 9 Replies View Related

Return Smallest Value, Larger Than Specified Value

Aug 15, 2006

I know function Vlookup finds the largest value less than or equal to the given value. However, If I want to finds the smallest value larger than or equal to the given value, How can I do it? assuming I do not want to use VBA.

View 5 Replies View Related

Sorting List Of Numbers - Some Are Larger Than Others

Jun 7, 2013

I'm having some problems sorting a list of numbers that some are larger than others, but it would sort it by how large the number is in regards to 123 or 45... 45 would go first instead of 123... I tried to change the column to numbers but it still won't work. as well as in the custom sort section. I'll try to upload a sample...

View 11 Replies View Related

Larger Than & Comparison Not Working When Used With A String

Apr 15, 2009

I have a macro that should be exceptionally simple and works fine up until the point where I try to use an If statement with a conditional.

I have run the macro up to the If statement, at which point FinalRow is 40 and StartRow is 6, so the If statement below should run but it doesn't. I've had this problem before and just given up, the only thing I can think of is that the If statement only compares the first digit in the String.

I've only copied a small part of the code as I don't think the rest is relevant and just muddies the page but I can paste the rest if needs be...

View 3 Replies View Related

Using Lookup Function In A Larger Formula

Nov 10, 2008

I am currently using the following formula:

=SUMPRODUCT(('Step 1_Data_Current Qtr'!T2:T10000=B2)*('Step 1_Data_Current Qtr'!C2:C10000<>"")*1)

...to count the number of instances in a data set where:

T2:T10000=B2 is counting records where the value in the cell is set to B2

and

C2:C10000<>"" is counting records where the value in the cell is not blank.

The current formular is working, but I want to change the formula so that the values in C2:C10000 is looking up specific names (in this case, physicians) and not just where the field is not blank. How can I change to lookup physicians in a specific list that may change, located in a separate tab from the main data set?

View 11 Replies View Related

Tell Larger Sequence Of Repeated Numbers In Row?

Nov 25, 2012

I have a table that receives the number sequence 1 and sequence number 0.

I need to calculate the highest sequence numbers that repeat in Secunia lines.

A
B
C
D
E
F
G
H
I
J
K
L

Result

1
1
1
0
0
1
0
1
0
1
1
0
3

[code].....

See for example the last line.

1 1 0 0 1 0 1 1 1 1 1 0 = 5

I have a sequence number 1, where it repeats 5 times segudamente.that is greater than other sequences.

View 3 Replies View Related

File Becomes Much Larger When Pictures Are Added

Jul 11, 2007

I have a form in excel that has a box which prompts you to add a picture when you click on it. It then resizes the picture so it fills the box. The file with no pictures is 2.3 MB. The picture is 700 KB. The file with the picture is 24.5 MB and 49 MB with 2 pictures. I need to E-Mail the form to other people but I can't send anything larger than 22 MB. I tried using th compress pictures option on the toolbar but it only took it down to 23.9 MB for a file with one picture. Why is it doing this and how can I make the file smaller?

View 9 Replies View Related

Determining Larger Value Cell & Combining With Another C

Aug 7, 2007

I have 5 columns AS-AW. Column AS contains a letter (either A, B, C, D, E, F, or G) which is generated by a VLookup formula. Columns AT-AW contain numbers (from 1 to 6) generated by a conditional formula. I need column AX to combine column AS with the smallest of columns AT-AW. Is this possible?

View 9 Replies View Related

The Size Of The Command Box Grows Larger

Apr 8, 2008

Each time he clicks on a command button, the size of the command box grows larger and larger until it eventually is covering over the display area of the report. Also, the other buttons along with the one he clicks grows larger.

View 9 Replies View Related

Taking Percentage Of The Larger Of Two Cells

Sep 19, 2006

I hope my title was clear enough, but Im not sure how to set this up. I have numbers in cells B4 and B13. I want to take the largest of these two numbers and multiply it by 0.85. I then want to take this number and compare it to a number in cell B23. If B23 is greater than the larger of B4 and B13 X 0.85, I want B33 to display No in a boldfaced red font. If B23 is less than the larger of B4 and B13 X 0.85, I want B33 to display Yes in a normal black font. This formula will be in cell B33.

View 5 Replies View Related

Macro Command Button Larger With Each Use

Mar 5, 2007

I have a command button in a spreadsheet that for some reason each time I click it, the button and text gets larger. I can edit the button to shrink it down again, but can't stop the growth...

View 6 Replies View Related

VBA Code To Remove Larger Values Between Two Delimiters

Feb 27, 2014

I have a set of data in column A that looks like this:

White:0:0|Counter Height:0:0|Orange:40:0|Counter Height:40:0|Green:40:0|Counter Height:40:0|White:40:0|Bar Height:40:0|Orange:80:0|Bar Height:80:0|Green:80:0|Bar Height:80:0|

The delimiter "|" separates the unique values of an item (in this example they are dining stools of differing heights and colors), I need to only keep the lower value and remove the larger one.

The example above contains both White:0:0 and White:40:0 as two different values associated with the same dining stool. I need to only have White:0:0 remain and White:40:0 to go away.

View 2 Replies View Related

Selecting Numbers Based On Larger / Smaller Than

Dec 12, 2012

I need a formula that states: If the number in A1 is larger than the number in C6, but smaller than the number in B6, select the number in D6. But, if the number in A1 is larger than the number in C5, but smaller than the number in B5, select the number in D5 and so on through Row 2.

A
B
C
D

1
#

2
0.4
10.0%

[Code] ....

View 2 Replies View Related

VLookup - How To Find Partial Match Within Larger String

Apr 9, 2014

I'm trying to lookup a 10 digit number against a string of numbers seperated by commas. And then return the Carrier Name and On-Time

LOOKUP TABLE
Order #(s) Carrier Name On-Time
5082940535,5082940507 Freight Lines Yes
5083055781,5083056150,5083056098 Ocean X No

Order # Carrier Name On-Time
5082940535 ? ?
5083056150 ? ?
5082940507 ? ?
5083056098 ? ?

View 5 Replies View Related







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