# Using InStr To Find Number Within A Number?

Jun 6, 2006
I work for a store and we're migrating to a new Point of Sale system. The new POS uses the entire UPC code while our existing one uses only partial UPCs, so I have to update from the partial to the full. ex. Partial 3378093 - Full 0033780935

The partial UPC will always be included in the full UPC, the difference is the dropped leading zeros (and possibly other numbers) and the check digit number at the end. What I have is a spreadsheet with with two worksheets (Old, New) - one has our old UPCs and other information, the other has the new, full UPCs and more information we'll need. What I want to do is write a function that will search through the full list of new UPCs to find the one with the old UPC inside it and return the full UPC. InStr seems the right function to do this - correct?

The part I'm having trouble with is writing something that will search through the full column of new UPCs using InStr. I've searched through the forum for a similar situation but the few I've found aren't working or appropriate. I don't want to include any code I've written because 1) it doesn't work and 2) it would be way too embarassing!

View 6 Replies
ADVERTISEMENT
Aug 20, 2013

I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.

I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.

View 9 Replies
View Related
Mar 1, 2012

Items in Column A1 are calculated by (B2/4+5)*1.4 Items located under the columns 2000, 3000, 4000, etc... 10,000 are calculated by taking the top number, eg 2000/(A1 cell value)+the column B number. 2000/7+0 = 286 (rounded numbers)

I need to find an way to look up for x number (2000,3000,4000, etc...) find the smallest number in that column and then return the value in column A1.

Cell

A1 Number >2000300040005000600070008000900010000

70 2864295717148571000114312861429

84 24236148059971883795610751194

[Code].....

View 2 Replies
View Related
Jun 3, 2014

I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.

View 1 Replies
View Related
Jun 4, 2014

I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.

examples.xlsxâ€Ž

View 1 Replies
View Related
Mar 26, 2014

I need a VBA code to find the nearest biggest number and nearest lowest number between the data of D2 to H2. In the attached file, I have mentioned my required output (Column A and B - blue highlighted)

View 2 Replies
View Related
Jun 2, 2008

to update these values via a form in this sheet. I can find the correct row to be edited by entering a value from column A and B. The problem is if I want display the values of that row first and then change it. If I want to change row 10 data how can I bring back the value in ROW 3 AND THE COLUMN VALUE? The next step would be to do the actual update if I want to change ROW 10 to "Ooi" and a sales value of 200?

This is what I have done so far:

Dim myRows As Integer

With Sheets("Mrt")

'Retrieve history information for row

For myRows = 4 To 49

If comboxDay.Text = Range("A" & myRows).Value And textboxdescription.Text = Range("B" & myRows).Value Then

textboxbedrag.Text = Range("C" & myRows).Value

chkBTW_Ja.Value = Range("D" & myRows).Value

txtNota.Text = Range("S" & myRows).Value

End If

Next

End With

Picture attached to show how sheet looks like.

View 9 Replies
View Related
Aug 26, 2008

I have a range of 100 numbers E10:CZ10.

If a 0 (zero) appears in this range to find the number before the number zero.

For example:

In cell AC10 I have the number 20

In cell AD11 I have a 0

I have another chart that I would like to list all the numbers that appear before the zero from the cell range E10:CZ10.

View 9 Replies
View Related
Sep 5, 2009

I have two sheets, sheet1 and sheet2. I would like it so that every time I click on the tab of sheet2 cell A1 of that sheet displays the row number of the last line in sheet1 that has the value "COST" inside column A, what would the coding look like?

View 9 Replies
View Related
Nov 12, 2009

I have an excel file, which contains following data:

Month

Department 1

Wages 10

Expenses 20

Preparations 15

Depreciations 3

All other 7

Transport 10

Total 65

Department 2

is there an easy way to find for example the transport cost of department 2 (=14)?

View 9 Replies
View Related
Sep 2, 2012

i have a table with multiple codes and quantities along with other info in it. What i need is to take each MATERIAL CODE and its lowest quanity and transfer it to another cell. I have tried using DMIN but can not quite get it. It stopped working after the 6th one.

103057300 -800

103057300 -700

103057300 -250

101789050 50

101789050 -70

101789050 -90

So i want to to take the following quanities that are in this font and put them into a separate cell.

View 1 Replies
View Related
Jan 27, 2009

I have a number in column “A” and I want to match them with column “D“, the number in column “C” and “D” is true value. find attached sample sheet for more detail.

View 6 Replies
View Related
Oct 13, 2009

Create a formula to find a number in a range. For instance, A1 is qty 1-100, any qty in this range,the cost charges is $10(B1).Whereas A2 is qty 101-200 and any qty in this range, cost charges is $8(B2).

C1 is the cell for me to enter the qty, if I enter 90, I want D1 to return as $10. If I enter 110, then D1 to return as $8.

View 4 Replies
View Related
Dec 4, 2009

I need to find which number in a row would be closest to zero, then display the associated name. The number can be negative. I used this, which works well if all the numbers are positive: =INDEX($C$1:$E$1,MATCH(MIN(C66:E66),C66:E66,0)). I'm running into a problem when the closest number is negative.

View 2 Replies
View Related
Jan 29, 2014

I have some code that sits behind the worksheet_change event that recognises that I have just inserted a row into the active worksheet and it is happily telling me that I inserted one row.

What I also need is the actual row number that I just inserted but I cannot seem to find the right syntax to return the value I need

View 4 Replies
View Related
Oct 7, 2009

I receive a workbook from multiple vendors on a weekly basis. Part of the data in the workbook is their production schedule for each part. I need to pull the quantity out of the cell containing their production schedule, but the problem is each vendor enters thier data differently, (as seen in the attached sample workbook).

I know I could request that each vendor comply to a new format I could send out, in which date and quantity are in seperate columns, but this format has been in use for a long time, and most of them have automated the output, so I thought I'd try to fix it on my end first.

View 4 Replies
View Related
Oct 3, 2011

In row 1 I have the numbers 1 through 7 running on a loop so A is 1 B is 2 .... F is 6 G is 7 and then H starts back over at 1 what i want to be able to do is find the last lets say number 2 in the row how would i do this?

View 2 Replies
View Related
Aug 17, 2012

Is is possible for excel to find a number in a cell and display it in a new cell?

A1 = AWS190B

A2 = AGZ160D

A3 = WGZ080D

Is there a command for excel to display 190 in A2?

B1 = 190

B2 = 160

B3 = 080 (or 80 is fine)

View 2 Replies
View Related
Mar 19, 2013

Trying to find if the number entered in the input box "num_cmp" is => Cells(i, 2) AND

View 4 Replies
View Related
Aug 14, 2013

Say i had a col of random numbers, and their corresponding probabilities of occurring;

-32.33

0.001497

-35

0.001523

-32.06876

0.001551

-29

0.001579

10

0.001607

7

0.001636

-31.54628

0.001665

-18

0.001695

Neglect the nature of the sample, its just gibberish and not of concern here. The question is, if i enter a value in column C, how do i find the closest corresponding number to it in col A, and subsequently return its nearest corresponding probability from col B? For example, say i entered 8 in C1, then the formula would need to find the closest number to 8 in col A, which is 7, and return from col B the value of 0.001636.

View 1 Replies
View Related
Jan 14, 2014

Let's say I have a value (N) in Cell A1 of Sheet 2 in Workbook A.

What I want to do is to Find the row number of the first cell and last cell that contains the value (N) in Sheet 1 of WorkBook B.

View 6 Replies
View Related
Jun 12, 2007

If I was to say have a date in cell A1, is there a formula which would tell me how many days from that date left in the financial year?

I was thinking an IF statement, but I know thats way off course! And it could get quite messy.

This is what I would like it to do:

Cell A1 - 30/10/2007

Formula - Finds the number of days between 30/10/2007 and the remainder of the financial year, which for me ends on 30/06/2007

Should give me the answer of "x" amount of days.

View 9 Replies
View Related
Aug 16, 2007

I'm having trouble coming up with a formula that will find the first month with a number in the 'Total' row (row 22) without using an array function or VBA. The months are in row 13 and can vary from 1 to a whole lot. The number of line items, column C, will also vary, but I wouldn't expect that to effect any function (aside from changing the row designation). Here's what I put together using an array function: {=MATCH(TRUE,ISNUMBER(D22:L22),0)}

Again, don't want to use an array function. Other people will be using this and when they mess with it, i don't want them asking me why it won't work anymore.

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutM14M15M16M17M18M19M20M21D22E22F22G22H22I22J22K22L22M22=

CDEFGHIJKLM12LineMonths*AROTot13123456789141*********0152**5*6*2**13163**4**3***71740000*****0185*421***5**30196***1***8*9207*4**9**3*16218*********022Total*

8301153711*

75Sheet1*

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies
View Related
Jan 16, 2008

how it is generated out of the oracle database. My question is instead of referencing "Supplier" could I look for any non-number then stop?

Columns("A:A").Find(What:="Supplier", LookIn:=xlValues).Activate

Range(ActiveCell.Offset(-1), ActiveCell.Offset(-1).End(xlUp)).EntireRow.Copy

Worksheets.Add after:=Sheets(Sheets.Count)

ActiveSheet.Paste

View 16 Replies
View Related
Nov 15, 2006

I have a range with numeric values formed from formula: =IF(D7<>"",D7*2,"").

I want ot find (select) the min number from this range.

In teory there shouldn’t be an 0 in the range as the formula produce "", but when I use vba WorksheetFunction.Min() it returns 0.

I also tried with small() function without any success.

View 9 Replies
View Related
Feb 14, 2007

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.

View 9 Replies
View Related
Feb 25, 2008

I'm trying to do in vba is search for a number and then return what the row number is for that number.

View 4 Replies
View Related
Aug 1, 2008

I have three columns with 1 number in each row. I'm trying to find a number, and if that number does not exist in the 3 columns I would like to find the next smaller number. The numbers have up to 4 decimal places. i.e. 16140.0311.

So for example if a user searches for 15950.012, and that does not exist but 15950.009 does with no numbers in between then the answer returned would be 15950.009. Auto Merged Post Until 24 Hrs Passes;I should probably mention that I would like to insert a new line with the number originally searched for, after the number found.

i.e. search for 15950.012. Not found. 15950.009 next lowest. Insert new line after 15950.009.

View 9 Replies
View Related
Dec 16, 2008

I have this set of number..

987654.

What im trying to do is this..I read this number and check for the first number with the 2nd number. If its greater then i will swap it. Im trying to do this so that my number can be rearranged as 456789.

View 8 Replies
View Related
Jan 25, 2014

I want to find the largest and smallest value for a number... E.g.

68734 the largest value is 87643 and the smallest value is 34678

39823 the largest value is 98332 and the smallest value is 23389

43089 the largest value is 98430 and the smallest value is 3489

View 4 Replies
View Related