Free Microsoft Excel 2013 Quick Reference

Formula to find matching number Results

I have a total of six columns. Each column has a number in it. These columns are divided into 2. EX. 123 345 ....3 is the matching number. What I need is the position of the matching number (which is 1) to be displayed in a separate column. Thanks to anyone that will help me.

I have a total of six columns divided in to 3 columns. Each column has a number in it. I need a formula to tell me which number from the 2nd 3 columns match the number or numbers from the 1st 3 columns.

EX.

123 367 ..... the #3 is a match. the number 3 is in the 1st position. I need a column to relay this information

I'm trying to use a single Excel formula to perform a somewhat complex task.

I have 2 columns of numbers, sorted from highest to lowest. Probably 70% of the numbers match each other side-by-side, because both are sorted the same way.

The remainder do not match. I need a formula that will compare values in both columns, then do one of 3 things.

The worksheet appears like so:
(A) (B)
21 21
33 25
90 33
129 129
130 140
145 145

1. If the column A value matches the column B value, output "match"

2. If the column A value does not match the column B value, AND the column A value does not appear anywhere in column B, output "no match possible"

3. If the column A value does not match the column B value, AND the column A value appears somewhere in column B, output "match elsewhere"

I'm trying to do this using the IF statement and it's a miserable failure. Any ideas how I might tackle this?

I'm an Excel newbie :-(

I have 2 columns - one represents width the other thickness
eg Width Thick
100 38
100 50
100 75
150 38 etc
In another column are 8 sets of similar figures, and I want to find if any of them match the figures in the 2 columns ie
100 45 would not be a match, but 100 50 would be. I have used a very complicated method to achieve this, but would like to use something like lookup or match to do it in one formula. I tried concatenating the values but did not know how to achieve it that way.
No sets of the figures incolumn1/2 are the same, but the same numbers repeat in invididual columns

Cheers

Barry

Hi All,

Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell
houses numeric single-digit or double-digit values.

I would like a Formula to find / match all instances of any two particular
numbers (single-digit / double-digit) in the same Row and Sum the Count of
their total occurrences together. For instance, how many times, if any, does
80 and 87 appear together in the same Row. I would like to refer to the
values using two cell references.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

I need help with a lookup function. I have an excel spreedsheet with 5
columns. , Clerk1, Clerk2....clerk5
The clerk fields contain text & numeric values (assignment codes). This is
a general field where any text can be inputted with the numeric value. I
need a formula to search all the columns, find a particular value (ie
"15.06") and identify which clerk is working on that assignment. In this
case, I would need the formula's value to equal "Clerk2" if I was looking up
"15.06". The columns are not sorted & contain a variety/combination of text
and numbers in each cell. All the numbers will have the decimal followed by
two digits.

Col A Col B

Row1 Clerk1 Clerk2
Row2 12.07 for JS 63.07, 15.06 due TR
Row3 899.07 & 701.07 for RO 229.07-231.07

row50 23.07 TR for RO 19.07 & 11.07

Please Help!!!
I am trying to have excel find the highest x number in a group of
numbers and sum them up.
Example:

Week 3
156
222
172
185
158
178
166
185
133

I need the Top 3 numbers summed up. (222,185,185) = 592

Changes Weekly:

Week 4
156
222
172
185
158
178
166
185
133
195
178
222

I need the top 4 numbers summed up. (222,222,195,185) = 824

This will go on for 44 weeks.

I need excel to pick the highest 3 numbers and sum them up. I cannot
use the large function because it will pick only 1 number and I need
the x number cannot change on it's own. I need it to pick 222,185,185
and sum them up I have a database with over 150+ columns which I will
have to sort using a macro and then apply the following formula. This
range will increase weekly and for every 3 numbers added, I will need
to pick 1 more of the highest numbers and sum them up. I thought I had
it by finding the x largest number example 4th(using x large formula)
highest number (185) and making a formula with
=SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9: C140,">"&C143)))
though, I found that if there is more than one number of the same
number, it will error. if i change the > to >=, it will pick too many
numbers. (C143 has the formula to find the x highest number(determined
in another cell).
I hope you understand what I am trying to do. It is very complicated
to me and I am now officially frustrated. Over 10 hours on this
(writing macros for sorting, etc.) Please help if you can. Thank You!

First time doing this.. but.. This is worth 30usd to me? (I'm guessing paypal is easiest?)

I haven't found online examples quite like what I'm trying to do, but I think it's do-able and quite easy?

I have a worksheet where each row is an individual. To the right of this I want some users to populate the employee numbers of who they feel could replace them if that individual left the company.

Because we want these replacements as employee numbers (so that we can populate additional sheets/do vlookups), and the number of possible candidates is quite large (1-2 thousand), I want to provide a search form they would use to find each replacement. This will allow more accurate data entry while still keeping the document in excel format, which they are familiar with.

So.. What the data looks like (as per first attachment example):

Sheet 1:
User,~extra data~,Replacement 1,Replacement 2, Replacement 3 etc....

Sheet 2:
Employee ID, First and last name, Last Name, First name, ~extra data~

Note that the last/first names on sheet two will be calculated (I'll use a formula to extra the First/Last names from the concatenated name in the 2nd cell). I'm doing this as I believe to allow searching on first/last name separately they should be in different columns.

Functionality desired:
Triggered by either clicking the "replacement" cells, or selecting the cell and starting the macro (ctrl function perhaps?) I'd like a search form to come up.

Search form would show 2 boxes:
-First Name
-Last Name
and a search button.

Entering either or both lists any matches (using wildcard search) and their matching employee number. If the user doubleclicks any of the results, that user's employee number is pasted into the originally highlighted "Replacement" cell.

Workflow Summary:
-User clicks one of the "replacement" cells on the first worksheet and starts the macro. If clicking the cell it'self would trigger the macro/form (or perhaps hitting a combination of keys) that would be ideal.

-Search form appears.. User enters first and or last name and clicks search.. matching results (based on first/last name from 2nd worksheet appear) **Note, would be desirable to show results as their first/lastname plus their employee number.

-User clicks the correct user (if there are multiple results).

-That employee's employee number gets populated to the cell they selected before starting the macro.

I've attached a simple workbook showing what the data looks like:

Closest I found to this was here:
http://www.ozgrid.com/forum/showthre...t=27799&page=2

But that one returns the sheet the matching user(s) is on, then brings you to that sheet/row if you click it.

Hi All,

Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell
houses numeric single-digit or double-digit values.

I would like a Formula to find / match all instances of any two particular
numbers (single-digit / double-digit) in the same Row and Sum the Count of
their total occurrences together. For instance, how many times, if any, does
80 and 87 appear together in the same Row. I would like to refer to the
values using two cell references.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

IF(SUMPRODUCT(--($A$2:$A$251=A2),--($D$2:$D$251=D2),--($E$2:$E$251=E2),--($C$2:$C$251=C2))=1,"","Duplicate")

Hi All,

I'm trying to find duplicate number in column A that has the same match with Product, Part, and Id. Somehow this formula doesn't work. Your input is greatly appreciate.

Please Help!!!
I am trying to have excel find the highest x number in a group of
numbers and sum them up.
Example:

Week 3
156
222
172
185
158
178
166
185
133

I need the Top 3 numbers summed up. (222,185,185) = 592

Changes Weekly:

Week 4
156
222
172
185
158
178
166
185
133
195
178
222

I need the top 4 numbers summed up. (222,222,195,185) = 824

This will go on for 44 weeks.

I need excel to pick the highest 3 numbers and sum them up. I cannot
use the large function because it will pick only 1 number and I need
the x number cannot change on it's own. I need it to pick 222,185,185
and sum them up I have a database with over 150+ columns which I will
have to sort using a macro and then apply the following formula. This
range will increase weekly and for every 3 numbers added, I will need
to pick 1 more of the highest numbers and sum them up. I thought I had
it by finding the x largest number example 4th(using x large formula)
highest number (185) and making a formula with
=SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9:C140,">"&C143)))
though, I found that if there is more than one number of the same
number, it will error. if i change the > to >=, it will pick too many
numbers. (C143 has the formula to find the x highest number(determined
in another cell).
I hope you understand what I am trying to do. It is very complicated
to me and I am now officially frustrated. Over 10 hours on this
(writing macros for sorting, etc.) Please help if you can. Thank You!

So I need some help and could not find this question anywhere. I have a range of 30 cells in a row. From that row there are groups of cells that have data that might be 1-7 cells populated in a row and in between these groups are blank cells in the row. I need to see what is the highest number of cells in a continuous row that contain data, what the mode is for continuous cell counts, and what is the average value for the data from the continuous cells. The average is a nice to have but not entirely necessary as most cell data will be fairly similar and I can get a close enough average just by knowing the average number of cells that contain data in a row. I have to do this calculation for about 1000 rows of individual data so its important a formula fit in one row.

Almost need a count function to count starting with each cell and then stopping at the first no no value it finds. Then it would show those counts and I could run a formula to pick the highest count and also show the mode of counts.

I will let the experts at it!, my weak mind could not think this one through. I thought something to do with MATCH for a while but no go from what I could do.

Thanks,

Brian

For every single unique entry in a column A of a table, I want to find the number of times it has a common entry in column B with each other unique value in column A! Sheet attached with an example. In the real data there might be as many as 10,000 rows.

For the first name in column A, john, I need to look at the value in B, 123, and see that steve also has an entry with 123 in column B.

For david and chris there are 3 values in column B which are common to both.

Hope this makes sense! I can change the layout of any column or table, pivot tables, standard formulae and macros are all fine (by fine I mean I am happy to use them, not necessarily that I know how!). The values in B can be alphanumeric, have spaces, and be any format really, just looking for unique values. Upper and lower case can be ignored. Column A will be all names, no digits. In the example it is sorted on B, but can move it around however.

So far I have been thinking about MATCH to go through each unique value of B taken from a separate pivot table, but can't make it work.

Any suggestions much appreciated,

thanks,
Tom

I am looking for a formula that matches two unique numbers in a table based on two numbers that are entered in two other cells. This will be clearer by looking at the example below and the attached.

The goal is to find the number entered for the home field (cell c2) in the "home" row (B5:K5) with the number entered for the away field (cell c3) in the "away" column (A6:A15) and result in the data element where the row and columns come together. The expected result for this example in the attached would be "JPD".

Another example would be if a 6 was in cell c2 and an 8 was in c3, the expected result would be "RBK". I think this can be done by indexing and matching but my memory has gone blank with how this works.

Any help would be SUPER...no pun.

Hi,

I can't seem to find the correct way of doing this. I've tried VLOOKUP, IF MATCH, and others, but I just can't get it to work correctly. What can I say, I'm "always confused".

I have two worksheets (Sheet1 & Sheet2). Both have just one column of numeric data (Column A in both sheets). I want to compare the two worksheets to find matches and output the results for each row.

Specifically, I want to compare the numbers in Column A on Sheet2 with the numbers in Column A of Sheet1 and output the result to Column B (new) on Sheet2 with a 'Found' or 'Not Found' for each row.

I would prefer a formula over a macro if possible.

Thanks!

I have 2 lists in which I need to find matching numbers then count the number of matches. The problem is these lists have empty cells for spacing purposes that span both columns and the formula is counting the empty cells as a "match".

My Formula would look like this

=SUMPRODUCT(--(E7:E25=F7:F25))

With the empty cells spanning across row 8 and row 23.

Any suggestions on how to ignore the empty spacer cells?

Hi I oversee meters, when a tech goes to one he swipes a card to identify the meter has been serviced. I have a report of times, meter #, meter areas and card #'s used. I need to identify who has been skipping meters.
I sorted the info by area and card # matching them up in the spread sheet, but i need a formula to count the number of meters where scaned by a certian card #. I need to search the info and have it give me the total # of meters where scaned by a card #. Can you help

Hello-

I am looking for help with a formula in the attached sample data spreadsheet. To describe it, basically I have a data set with a bunch of order numbers in column A and their values going out by date in columns B through whatever depending on the date. In the other sheet I have a list of Order numbers and am trying to write a formula to return the first value for that order that is not zero.

Here is my current formula, which does not work.

Unfortunatley, the way the data is maintained, I cannot get the list of orders in the WORKSHEET tab to be in the same order
as the DATA tab, so a formula around this problem would be a great help.

Many thanks in advance for your help!!!

Hi everyone,

I have a strip of alphabetical and numerical values across a row contained in cells B6 to J6 as follows:

56 dnp 24 dnp dnp 47 dnp 33 dnp

Thanks to this site, I have previously used the following formula to find the LAST numeric value in this range:

=INDEX(B6:J6,MATCH(9E+300,B6:J6))

I have also used the following formula to find the SECOND LAST numeric value in this range:

=LOOKUP(9E+300,B6:INDEX(B6:J6,MATCH(9E+300,B6:J6)-1))

I now need to find the THIRD LAST numeric value in this range. I tried to use the formula:

=LOOKUP(9E+300,B6:INDEX(B6:J6,MATCH(9E+300,B6:J6)-2))

but this did not give me the right answer. I have a number of rows of data just like the above strip (56 dnp 24 dnp etc etc) but with different combinations of numbers and "dnp" entries, and the formula above works on most of these strips, however when there is a certain combination of numbers and "dnp" entries across the columns (such as the one above), the formula for the THIRD LAST numeric value does not work correctly.

Does anyone know whether there is a formula to determine the THIRD LAST numeric value in a row, regardless of where the "dnp" entries are located within the row of data?

Thanks in advance...

I am using the formula below to populate a column displaying row numbers from a table. But I cannot get it to populate the column. The file attached should make my problem clearer.

Any help would be greatly appreciated.

Jim O


No luck finding an answer? You could always try Google.