# Find Whole Numbers In Row Of Data?

Apr 9, 2014##UPDATED## Hello

understanding the IF forumlas to calculate rows with whole numbers in them for the last columns

CBOOK1.xlsxBOOK1.xlsx

##UPDATED## Hello

understanding the IF forumlas to calculate rows with whole numbers in them for the last columns

CBOOK1.xlsxBOOK1.xlsx

ADVERTISEMENT

I have two columns of numbers (this week - Column A and last week - Column B). What I need to do is look at the numbers and pull two lists out in Columns C and D. In columns C a list of the numbers that are missing from last week in column D numbers that are missing that were added from the previous week. The two lists consist of about ten thousand rows of data.

******** language="JavaScript" ************************************************************************>

Microsoft Excel - Book3___Running: 14.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

(A)boutA1=ABCD1ThisÂ WeekLastÂ WeekMissingAdded

21002103110311002310271032103210274103310331034104051035103410361055610371036Â

165571038Â Â Â 81040Â Â Â 91055Â Â Â 101655Â Â Â Sheet1Â [HtmlMaker 2.42]

The first 7 digit number is the input to a certain equation. This equation outputs the 7 digit number after the space.

Input (7146127) ==> Equation ( Unknown) ==>Output (2871082)

These are some data that I have ( inputs and outputs). I was wondering if there is anyway to find this equation. I can get more data if that helps in finding the equation.

71461272871082

53820056426572

61070709150530

75833373805331

55466908456008

49264716615511

12387558353260

20013928484175

67975657084961

38006283229553

68973249414920

65179634653276

66971872613128

59646203411273

69785611400836

58284602307740

64723009925464

57930863495088

58816593932906

62736335606616

52119154778580

60043359437317

61921553996320

51507747216899

13197956063818

64210347411256

65399671193442

55656585589500

55148886441637

30850639587521

69081133766387

54139417804940

53408393698534

62310293191095

30512556590168

14514168265124

89078208134056

85177364363577

68714858216023

17584065849687

52691521917548

9840524785846

59268687494601

89374963791119

45989738961270

65040033345415

15061518946940

55392084949214

45103824265242

84181944909073

The format of the text in which I need to extract numbers is as follows:

23411268 - 23411270

Need to extract the following:

23411268

23411269

23411270

These numbers have to be listed in three seperate rows.

I want to create a formula that values if the number in the cell is in the biggest 25 numbers in the column and also check in another column the same thing.If the number is within the 25 biggest from the first column or the second then to give me the number of the cell. I created a formula but it is very large in order to apply for the 1st to the 25th biggest number. The formula is:

IF(AND(or(K16=LARGE($K$16:$K$152,1),I16=LARGE($I$16:$I$152,1)),K16<>0),K16,"") but only for the 1st biggest number of the columns.

I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.

The sheet looks something like this, numbers are all in one row:

7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32

I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.

I have a workbook that has 3 columns (G, H I) with numbers from 1 to 7 in each column. I want to tell excel that if a row has a particular 3 numbers in these cells then change it to a specified number. I want to do this for the entire workbook. Is this possible?

View 3 Replies View Relateda macro finding the max number in col B in each day (hr 0- hr 23) and in col C put a "1" next to it.

View 10 Replies View RelatedI have 300 number groups and each group has 22 different numbers (from 1 to 80).

Can I find the most common 5 or more numbers in these groups by excel, macro or any program?

for example, let us suppose that 4-15-23-36-45 are the most common numbers and these numbers are in 8 groups from 300.

I have a list of numbers in a column and I need to find which numbers

when summed together equal a figure. I have a list of invoice amounts

that I need to match up with payments (the payments are always made for

several invoices so I need to come up with sums of several invoices to

get to this payment amount).

An example would be I have this in the following section (A1:A10):

$17,213.82

$4,563.02

$85,693.42

$1,166.01

$725.90

$580.09

$2,243.75

$240.16

$207.70

$725.90

I need to find which combination of these figures would sum $1,173.76.

I have a file with 2 worksheets: Worksheet "Data" contains Store Locations, Store Numbers, Categories, Brands, Sales, Clusters, % Demand, etc.

Under % Demand on Worksheet "Data", I need a formula to look for % Demand from Worksheet "Stores" that related to the same categories, brands and stores numbers.

(1) i have a project of a series of numbers running left to right in a random order what i am after is to sort them into order

(2)i want to find the frequenzy on numbers and how often they apear with other numbers compairing one line to the rest all the way threw

I have a file with approx 3600 rows. In Col B there is a 13 digit EAN-13 code. I would like to find out which numbers are missing. I have to compare each cell from top to bottom in the B-column to the cell below and find out if there are some missing numbers. If the numbers are incremental then goto next cell. But if there's a gap in the numbers then it should insert the missing numbers with an increment of 1.

I think the logic statement should look like this:

i = 1

For x = 1 to last row

If the gap between B(i) and B(i+1) < 1 then

Insert a row and put the missing value in the cell on the new row.

Endif

i = i+1

Next x

The comparison has to be made from the 5 digits in place 8-12 in the code.

Here's an example (i've dimmed the digits that shouldn't be compared):

7330985920714

7330985920721

7330985920738

7330985920776

As you see there's a missing number between the third and fourth row. Here the value should be 733098592074 + the checksum that is a bit tricky to calculate.

Between the fifth and sixth row there are four missing numbers that has to be filled in.

The checksum is OK to leave out at this moment. The important thing is to get the 5 digits.

I have a very large database (42455 lines) which has three columns. The column A is a shipment number which is a combination of numbers and letters, column B is a ship date, column C is a dollar value for each shipment. This range (A2:C42455) is named SHIPMENTS. I have a second database right next to it that contains warranty information. Column D has the shipment number, Column E has the date the warranty call came in. These last two columns have only 2788 lines.

I'm using VLOOKUP in column F to match Ship dates with Warranty Call dates. The formula is:

=VLOOKUP(D2,SHIPMENTS,2,False)

and was dragged down all 2788 lines.

Out of the 2788 lines it did not find dates for 301 of them. Some of those 301 have Shipment numbers that are only numbers (no letters). As a test I copied one of the "unfound and all numbers" shipment numbers and did a control+F for all of column A and it found it. Clearly VLOOKUP is missing some of the Shipment numbers. I made sure that the Shipment number in column A matches the number in column D by cancelling out of the Find window and clicking on the found cell. Then I made sure there are no stray spaces tacked onto the end of the number and there were none. I hit enter.

At this point excel starts calculating again and when I go back up to the VLOOKUP (In column F) it has now found the data I've asked it for originally. I've not changed any of the values of either cell...All I've done is use Control+F to manually find the Shipment number in SHIPMENTS and then verified that there were no spaces in the found cell by clicking on it and using the arrow keys. Once that was done I hit enter.

Somehow this sequence of actions allows VLOOKUP to now find the cell.

Example:

I have a worksheet that has a column with amounts in it. I would like to find the numbers in that column that add up to a certain amount.

I am trying to find some number which i have from a list of numbers available in various rows, for eg

11

7

11

13

17

i am tryng to find 11,13,14,15,16.... 38 from columns given i.e. 7,11,13,17....

I have two spreadsheets. Spreadsheet 1 has 6 columns and column E has a list of numbers. It runs from E2:E739.

Spreadsheet 2 has 9 columns and column A has the numbers I need matched against. Column A runs from A9:A5027. How can I find the numbers from Spreadsheet 1, Column E that are also in Spreadsheet 2, Column A. Once matched I would like to highlight the ROW on spreadsheet 1 that has a match.

I currently have: ...

View 8 Replies View RelatedI have a bunch of phone numbers that I'm trying to reformat.

Say I wanted to replace any phone number like this: 1##########

With this: ##########

Keeping the numbers intact, just getting rid of the 1 in front of them. Can't figure it out for the life of me.

I've got a list of numbers and want to show the difference between the highest number and each of the other numbers. Here's an example: (The first column is the position, the second column is the number and the third column is the difference.)

1 - 20

2 - 18 - 2

3 - 15 - 5

4 - 14 - 6

5 - 11 - 9

Please refer to attached sheet.

I have numbers in column A thru G.

I need unique numbers from this list in column J as shown.

I need to find the minimum of 4 numbers placed in 4 columns and delete the one which is minimum among them. I know to find minimum I can use min function but i don't know after finding how can i delete that automatically. Also problem is if two or more numbers are identical and minimum value it has to delete any one of them.

View 4 Replies View RelatedI have a spreadsheet with a stock list. Each item has an individual number. I have a code below, which I have doctored from other various sources. The problem I have is that if I search for number '12' the spreadsheet highlights every cell with 12 in (120 121 122 123 etc...) Is there any way I can change the code so that it returns only the 12 and not every cell that contains 12? The current code is as follows:

View 2 Replies View Relatedi had a worksheet in which i had a column Named Cheque No. i want to print the missing cheque no's in a series of cheque no's.The logic here is that there will be different series of cheque nos for different transactions.Just check out my worksheet.

View 9 Replies View RelatedI want to input numbers into a master sheet by having it referenced to a name. For example; I will be inputting a report on monthly sales, which contains a company name, and then a value. However, this report doesn't always necessarily contain all the companies listed on the master. I want to simply put this report on a page, and have the referencing pull the numebrs to the approriate company on the master page.

In the attachment - I want the numbers from Sheet 2 to be entered into their appropriate match in the Master sheet (sheet 1) - without copy and pasting (becuase in reality, I have 200+ company names and numers I'm dealing with).

How do you tell Excel to find a sum out of a partial list of numbers? Let's say, I have sent 10 invoices with different totals to a client, and he returns payment for only 5 of those invoices without any reference. I need to know which invoices are paid.

Is there a function that can do this?

I know this is probably really easy to do but I am new with excel. I have a list of 8 numers in 8 cells and was able to find the lowest. But now I need to take the list and subtract the lowest number from the second lowest and have the difference dispay in the target cell.

View 8 Replies View RelatedSheet4 in my book looks like this:

.............A.......................B................................C..................

I......Invoice No…..…Starting Serial No……...……Ending Serial No

2…………ND12………..6011000000013652……. ..6011000000103652

3…………ND13………..6011000000116526……....6011000000246977

4…………RF4……….….6006000001255724……..…6006000011249564

5.........RF4...........6005000000023652.........6005000000095624

note that the last 4 numbers of the series are randomly generated for security reasons and are not part of the range of the the serial No’s

So Invoice ND = 10 consecutive numbers, Invoice ND13 = 14 and Invoice RF4 = 1000 and RF4(again) = 8

The list on this sheet is a mile long

What I need is a “search box” with two windows in it

In one box I will type a serial number and in the second box I will get the Invoice number

The challenge is that the serial number could be between Starting and Ending serial No’s thus if I were to type serial number

6006000001086598 in the first box the answer in the second box will be : RF4

Is there any way to find where in a row of several columns, the total will equal a specific number or cell?

View 9 Replies View RelatedIf I have A1 = 1, A2 = 3, A3 = 5, A4 = 7, and A5 = 9 in column A, Would someone know how to get the combination of numbers in column A that equal 13, or 24 for example?

View 9 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved