Free Microsoft Excel 2013 Quick Reference

Matching a combination of numbers Results

HOw can I match combination columns of A-F (row1) to combination H-M (rows1 to end) and show how
many are number matched in column N..





























































hello all,
i was not able to find the answer to my specific questions in Search, so if the question has already been asked & solved, please help direct me to the previous thread. otherwise, i would greatly appreciate your help.

to compile a list of top 50 selling product part numbers (ItemNumber) & corresponding total sales dollars (TotalSales)

there are duplicate ItemNumber b/c each ItemNumber has the potential to be found in one, several, or all five warehouses (WhseCode).

desired outcome:
in order to have accurate sales data for each individual ItemNumber i assume I would have to sort data ascending by ItemNumber, find & match duplicate ItemNumber, combine warehouse codes into that one cell to reflect all contributing warehouses, add amounts of PriorYrQtySold, add $ amounts of the PriorYrDollarsSold and put value into an new column/field TotalSales, and delete the extra row(s) of data that was just added to the first row.

fortunately, when duplicate ItemNumber occur b/c of diff warehouses, they still have the same Cateogy2 value(ex: CATALOG, PRIVATE, several others). i would like to avoid having to sort data ascending by category, and delete everything that isnt one of the two desired values. is there an easier way to ask Excel to delete rows whose Category2 values do not equal CATALOG or PRIVATE?

fields used (in column order):
ItemNumber alphanumeric (ex: 11420, 11520USED)
Category2 - has about 10 options, Im interested in (ex: CATLOG, PRIVATE)
WhseCode - 5 options (ex: FGA, FGM, FGI, WEB, FGW)
PriorYrQtySold - negative/positive number
PriorYrDollarsSold - negative/positive dollar amount with no ($) sign used
TotalSales column not created yet

12345, CATALOG, FGA, 2, 20
12345, CATALOG, ZZZ, 3, 30
12345, CATALOG, FGM, 4, 40

desired outcome of example:
12345, CATALOG, FGA ZZZ FGM, 9, 90

please let me know if you need any more information. thanks for your help!

I have a formula in column G of my worksheet as follows. The formula produces the desired result


I have a formula in column H of my worksheet as follows. Where there is a number containing Alpha characters in column G within the number it produces the desired result-see formula below


Where there is a number containing numbers only in column G < i have to amend my formula in column H -see formula below

I need a formula that will combine the two formula so that if the number is G12 is for eg 10Z999 or 101014, the formula will work in both instances, instead of having to manully change the formulas.

I have attached a sample. Your assistance will be most appreciated


******** ******************** ************************************************************************>Microsoft Excel - lookup Values.1.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG12H12G13H13G14H14G15H15G16H16G17H17G18H18G19H19G20H20G21H21G22H22G23H23G24H24G25H25G26H26=
EFGH126X|10Z999-2540100213 | 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I have a file that combines multiple years data to generate a mailing list. I want only one mailing going and I want it to be the most recent data here is sample of the file

******** ******************** ************************************************************************>Microsoft Excel - 08 license mailing list with 03 SO.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA998=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

Where col A is a member number (this is not always entered properly or we could just use it to weed out the previous year info) Here is program I put together to do that but for some reason it does not work very well ie it leaves some that are matching numbers ??? The other issue si a member number may be assigned to another rider in future years so I sort it by name then check the member number.

Sub DuplicateRiderListprt2()
Rem for master mailing list
Range("A1:L9864").Sort Key1:=Range("b1"), Order1:=xlAscending, Key2:= _
Range("G1"), Order2:=xlAscending, Key3:=Range("F1"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
Application.ScreenUpdating = False

Dim LR&, LC%, x&
With Range("A1").CurrentRegion
LR = .Rows.Count
LC = .Columns.Count
.Interior.ColorIndex = 0
End With

For x = LR - 1 To 2 Step -1
If Cells(x, 1).Value = Cells(x + 1, 1).Value Then
If Cells(x, 1).Value Cells(x - 1, 1).Value Then

End If
End If
Next x

I then have this program to check first name at an address and remove those duplicates but again it seems to remove only some from the list.

Sub DuplicateRiderList()
Rem for master mailing list
Range("A1:L9864").Sort Key1:=Range("b1"), Order1:=xlAscending, Key2:= _
Range("G1"), Order2:=xlAscending, Key3:=Range("F1"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
Application.ScreenUpdating = False

Dim LR&, LC%, x&
With Range("A1").CurrentRegion
LR = .Rows.Count
LC = .Columns.Count
.Interior.ColorIndex = 0
End With

For x = LR - 1 To 2 Step -1
If Cells(x, 6).Value & Cells(x, 8).Value = Cells(x + 1, 6).Value & Cells(x + 1, 8).Value Then
If Cells(x, 6).Value & Cells(x, 8).Value Cells(x - 1, 6).Value & Cells(x - 1, 8).Value Then

End If
End If
Next x
End Sub

As an FYI this list was 11,000 entries and was pared down to 8,000 then 7,790 but I as you can see there are lots of duplicates still.

This has me really confused as it removes some but not others if it totally did not work then that would be one thing

Oh and yes I aware that St and St. are diferent and that I will have to sort those out manually same with Ln vs Lane

Hello All,

I just got done making a nice little utility that I'd like to share. I apologize if this is in the wrong place. I'm just hoping it can help.

I'm a new dad and I have a few problems: I take a lot of photos and don't have time anymore to rename them. Since the camera keeps going back to 0001 every time I move the photos to my computer, I have a lot of folders with photos that are the same name.

Additionally, the dates that they show aren't the dates the photo was taken -- I like to use the dates to help organize my photos.

The below code will ask you for a folder with photos then put them each on a line along with the date taken, a column will have a new name for the files, if the files are from my cameras it will make a completely new name for the photo based on the date taken and sequentially number each photo taken on that date. For those with a unique name already, it will add in the date taken.

Then it asks if you'd like to copy the photos to a new folder (with the new names).

It's probably pretty clunky and it probably can be a lot better, but it's good enough for me and probably has a few things people can learn from and use.

I hope it helps.


Sub Get_Folder_Contents()
' There were many coders who helped make this, it was an involved process combining bits from all the code
' I surfed. jmhans deserves most of the credit for this first Sub. You can the original code at:

' I had a lot of starts and stops to get it this far and it's pretty clunky and will probably crash
' It works on my computer though and hopefully it can help others.  I'm sorry I can't give credit to all
' those who helped. Good luck and I hope it helps.

    Dim fd As FileDialog
    Dim folderstring As String
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
    End With
    folderstring = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
    f = Dir(CStr(folderstring & "*.*"))
    n = Range("A65000").End(xlUp).Row + 1
    ' nNumber is the counter for the file numbering scheme
    nNumber = 0
    Do While Len(f) > 0
        Cells(n, 1) = f
        'Cells(n, 2) = Right(folderstring, Len(folderstring) - InStrRev(folderstring, ""))
        Cells(n, 2) = folderstring
        Dim fs, fl
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set fl = fs.GetFile(f)
        ' This part calls the Sub to determine the Photo Taken Date
        Dim FileName As String
            FileName = Range("B" & n) & "" & Range("A" & n)
            Call GetPhotoDateTaken(FileName, n)
        Cells(n, 3) = fl.DateLastModified 'This is here to show the difference between the two dates
        With ThisWorkbook.Sheets("Sheet1").Range("D" & n)
            .NumberFormat = "yymmdd"
        End With
        'The left section looks for the code that identifies those files straight from the camera
        'That way if you've already named some of them it won't re-rename them.
        If Left(Cells(n, 1), 4) = "SANY" Or Left(Cells(n, 1), 4) = "DSCF" Then
            chk1 = Format(Cells(n, 4), "yymmdd")
            chk2 = Format(Cells(n - 1, 4), "yymmdd")
            ' Below checks to see if the date of the current file does not match the one above
            ' If they don't match then it must be the first date of the photos
            If chk1  chk2 Then
                nNumber = 1
                Cells(n, 5) = chk1 & "-" & Format(nNumber, "000") & ".jpg"
            Else 'If they do match, then it is the next number in the sequence
                Cells(n, 5) = chk1 & "-" & Format(nNumber + 1, "000") & ".jpg"
                nNumber = nNumber + 1
            End If
        Else 'If the photo has been renamed, this will put the date taken in the name making it easier to sort
            ' Delete the Else to the End If (leaving End If) if you don't want them renamed
            chk1 = Format(Cells(n, 4), "yymmdd")
            Cells(n, 5) = chk1 & "-" & Cells(n, 1)
        End If
'' For number format of sequential numbering--Format(nNumber, "000") and on the next line nNumber = nNumber + 1
        f = Dir()
        n = n + 1

'This can only do one folder at a time, so this sees if you'd like to do more folders and sends to beginning if yes
If MsgBox("Would you like to do another folder?", vbYesNo, "Ask for another folder...") = vbYes Then
GoTo Start
End If

'This checks to see if you're ready to being the copy process
If MsgBox("Would you like to copy the files at this time?", vbYesNo, "Copy Request...") = vbYes Then
Call CopyFiles

End Sub

Sub CopyFiles()

Dim SourceFile, DestinationFile
    Dim fd As FileDialog
    Dim folderstring As String
    MsgBox "In the next dialogue box, please choose the folder you'd like the photos *copied* to..."
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
    End With
    folderstring = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

For x = 2 To Range("A65000").End(xlUp).Row

SourceFile = Range("B" & x) & "" & Range("A" & x)    ' The file to be copied is in Column A.
DestinationFile = folderstring & "" & Range("E" & x)   ' The new file location and new file name.

FileCopy SourceFile, DestinationFile    ' Copies files from source to target.
'Below is for testing...shows name of each gets old with lots of photos,
'but you can check your work before moving your files
' MsgBox SourceFile & Chr(10) & DestinationFile

Next x

End Sub

Sub GetPhotoDateTaken(FileName As String, n)
' This is taken from Duke who wrote in to CodeNewsGroups.Net 17 Jun 2006 6:21am and
' modified slightly to fit this requirement. The original code (as of 12 Aug 07) is at:

   Dim Buffer As String
   Dim YY As String
   Dim MM As String
   Dim DD As String
   Dim HR As String
   Dim MN As String
   Dim SC As String
   Dim DT As String

   DateTaken = 0
   Open FileName For Binary As #1

   Do Until Len(Buffer) > 120
     Line Input #1, Buffer
     If Loc(1) = LOF(1) Then
       Exit Do
     End If
     If Len(Buffer) = 19 Then ' I believe that 19 is the location of the Date Taken info
       YY = Mid(Buffer, 1, 4)
       MM = Mid(Buffer, 6, 2)
       DD = Mid(Buffer, 9, 2)
       HR = Mid(Buffer, 12, 2)
       MN = Mid(Buffer, 15, 2)
       SC = Mid(Buffer, 18, 2)
       DT = MM & "/" & DD & "/" & YY & " " & HR & ":" & MN & ":" & SC
       If IsDate(DT) Then
         DateTaken = DT
         Cells(n, 4) = DT
         Exit Do
       End If
     End If

   Close #1

End Sub

Hi everyone,

I will give this request one last try and put it as simply as I can without all the confusing and complicated jargon.
I have the following wheel of 6 number combinations in Cells "B3:G12" in a sheet named "Data".

01 02 03 04 05 06
01 07 08 09 10 11
01 12 13 14 15 16
02 03 07 08 12 13
02 03 09 10 14 15
04 05 07 08 14 15
04 05 09 10 12 13
02 03 04 05 11 16
06 07 08 09 10 16
06 11 12 13 14 15

I want to iterate through ( see the code below ) ALL the 5 number combinations produced from "n" ( "n" = the highest number used in ANY of the 6 number combinations which is 16, so C(16,5) = 4,368 combinations ) and compare each 5 number combination with each 6 number combination in the wheel above.

If at "LEAST" 2 numbers match then add 1 to the 2 if 5 category total ( total to go in Cell "D12" in the sheet named " Statistics" ).
If at "LEAST" 3 numbers match then add 1 to the 3 if 5 category total ( total to go in Cell "D16" in the sheet named "Statistics" ).
If at "LEAST" 4 numbers match then add 1 to the 4 if 5 category total ( total to go in Cell "D19" in the sheet named "Statistics" ).
If ALL 5 numbers match then add 1 to the 5 if 5 category total ( total to go in Cell "D21" in the sheet named "Statistics" ).

If ANY of the 5 number combinations does NOT match ANY of the 6 number combinations with the required scenario of ? if 5, then that 5 number combination is NOT covered for that particular scenario of ? if 5 and therefore 1 ( one ) cannot be added to the respective ? if 5 category total.

That is it in a nutshell really.

I have written the following code which might be of use :-

Option Explicit 
Option Base 1 
Sub Produce_5_Number_Combinations() 
    Dim A As Integer 
    Dim B As Integer 
    Dim C As Integer 
    Dim D As Integer 
    Dim E As Integer 
    Dim MinVal As Integer 
    Dim MaxVal As Integer 
    Application. ScreenUpdating = False 
    MinVal = 1 
    MaxVal = Whatever the highest number In the sheet named "Data" And In the Range "B3:G?" is. 
    For A = 1 To MaxVal - 4 
        For B = A + 1 To MaxVal - 3 
            For C = B + 1 To MaxVal - 2 
                For D = C + 1 To MaxVal - 1 
                    For E = D + 1 To MaxVal 
                        *** Code goes here maybe *** 
                    Next E 
                Next D 
            Next C 
        Next B 
    Next A 
    *** Output totals For Each category *** 
    Application.ScreenUpdating = True 
End Sub
Thanks in Advance.

Can I use a vlookup or hlookup formula to scan a range that is not in order? Or do I need to use an index/match combination? I have a range that is input by a user (numeric part numbers), but not in any order. Currently, my vlookup function "=VLOOKUP($B116,$B$7:$G$104,2)" works only if column B is in numerical order. If I am looking for a value of 12335, as soon as I have a sequence like:


the function returns the dreaded #N/A. If the list is in numerical order, it wroks just fine... HELP?


Can I use the Countif function to count an unique entry in multiple worksheets?? I have two separate worksheets for the two fortnights of the month. One for the first fifteeen days and the other for the next fifteen days. I would like to count the number of entries matching a particluar criteria in both the sheets at the same time. All the conditions like the range, the criteria remain the same. The only thing that changes is the names of the worksheets. And I don't want to combine the worksheets in to one. Can I do it??? Please Help!!!!

Im trying out MrExcels Easy-XL and for the life of me, Im having a heck of a time getting my head wrapped around a simple function like MERGE

I have a list of products that somehow got copied a few times and various work was done on each of the 4 copies! Now what I need to do is combine them based on the (product number) and simply append all the data.

Some sheets might have for example part of the dept or sub catagories done, whereas another sheet might have the Mfgrs all updated, another might have the Vendors we buy them from updated ..

and what I have to do is just put them all together.

Given the 4 "MERGE OPTIONS" available (to output rows with the types of matches) ... for some reason Im just getting totally goofed!

1 Rows in sheet 1 that match a row in sheet 2
2. Rows in sheet 1 that DONT match a row in sheet2, (columns combine from sheet 2 will be blank)
3 Rows in sheet 2 that match a row in sheet 1
4. Rows in sheet 2 that DONT match a row in sheet1, (columns combine from sheet 1 will be blank)

I just need the idiotproof version / way of doing this ...
I was just hoping to Merge!

Sorry .. I know in a week, Ill look back at this and be totally ashamed of myself!


Hi Folks,

I use this formula

on Sheets Mars in Column A and B is the criteria, in Column E is the Value.
Criteria 1 (Column A) is Text, eg. "Aa"
Criteria 2 (Column B) are numbers whitch must match Column C (of the sheet were the formula is)

The combination of the two criteria's is always unique.

But, here is the catch, it should return the value in Column E on sheet Mars, however, this is text.

How can I return a Text value with two criteria's from an array/Dbase



to have 1200 unique 2 letter combinations you need about 35 unique characters.
this normally would not be done with alphnumeric because of the similarities
between 0 Zero and the letter O and 1 (one) and the letter I Combining lower
case and upper case letters is a possibility.

From your title is sounds as though you want to gererate them randomly
rather than just assigning them.
What I would to to generate them and get them into a random order is to
first enter in a column (A?) a,b,c,...X,Y,Z (one letter per cell). I would
fill this column with copies down to 2704. I would then copy this column to
the next column (B?)
and in a third Column (c?) enter in C1 =A1 & B1, and copy this cell down to
In a fourth coulmn (D?) I would enter =Rand() in D1 and copy this down to
then selecting Columns ABCD and sorting by Column D You would have in column
C a list of 2704 unique 2 letter identifiers. Note I would select all and
paste values over the top of the calculations.

If you can't use the lower and upper case characters, you will need to
establish what your needed unique characters are and just adjust the limits
of he cells by whatever number of characters you end up with

"DBuche" wrote:

> I need a way to generate over 1200 unique two letter combinations. I've been
> reading past posts that explain ways to use the MATCH and VLOOKUP functions
> to convert numbers to text, but have not found the solution yet. Any help
> would be greatly appreciated.

I have two list. One list has about 150 rows of data. Each row has a serial
number. My other list has about 800 rows of additonal data and each row has
serial numbers as well. I would like to find the rows in the larger list
that have same serial numbers as in the smaller list and combine these rows
into one. How is this done? Basically, I want to find and match the serial
numbers to import data from the larger list into the smaller list.

I work with chemical formulas which include subscript formatting i.e.
H2O (the 2 should be subscript. Even here I can't get the subscript to
follow with copy/paste). I work in Excel 2003

I want to pull from one table to another , but I need it to pull the
subscripted character as is. I've tried to find a similar thread and
have worked with INDEX - MATCH combos but no success. VLOOKUP has
certainly not worked. It pulls the text but without the subscripting.

Is there some combination of functions which will pull text as
formatted from one table to another?

I thought I might have been onto something with this but I get #VALUE
as result.

number = lookup value jde = lookup array

If I replace "text" with "format" I get G (general) although the cell
in original table is text.

If functions don't work can VBA help?

We have discovered a problem in referencing time using the LOOKUP
function and I haven't been able to resolve it using VLOOKUP or the
INDEX and MATCH combination

The easiest way to demonstrate the problem is to creat a list of times
from 10:00 am to 12:00 pm in 15 minute increments, then copy the list
into the adjacent column and convert that list into the decimal
equivalent. See below for what the list should look like. I expanded
the decimals to the maximum number of decimals.

10:00 0.416666666666667
10:15 0.427083333333333
10:30 0.437500000000000
10:45 0.447916666666667
11:00 0.458333333333333
11:15 0.468750000000000
11:30 0.479166666666667
11:45 0.489583333333333
12:00 0.500000000000000

Now if you write a formula using LOOKUP or VLOOKUP to try to return the
decimal equivalent of a time in the left column. It works fine except
at a few times like 10:45 and 11:30 when it returns the values for
10:30 and 11:15 respectively.

Any help would be appreciated.


jjhmbh's Profile:
View this thread:

In cells A2:A9 are the following names/titles #1 ,#2, #3, #4, #5 ,#N1, #N2
& #O2

in cells b2:b9 and then c2:c9 and then d2:d9 and so on and so I would like
to be able to save some typing amd have an auto check done. As it is right
now I type all the text and sometimes I put the wrong text in the wrong cell,
so no matter what text I enter in a cell the corresponding/related text will
get entered in the correct corresponding cell.

for example
when I enter text xxxP4 in cell B2 automatically have xxxP1 show up in
cell B5
when I enter text xxxHN1 in cell B3 automatically have xxxG2 show up in
cell B7
when I enter text xxxGN2 in cell B9 automatically have xxxHO1 show up in
cell B8
when I enter text xxxH3 in cell B6 automatically have xxxG5 show up in cell
the same would then apply to each suscessive column if I enter text for that
column, I could enter any one of the possible 21 combinations in any cell and
would like that the corresponding combination autofills in the corresponding

xxx will be anywhere from a 1 to 3 digit number with the potential for a
alpha character at the end the range of xxx is 7A, 73A, 8A, 9A, 10, 11, 12,
123, 1, 13, 2, 23, 3, 4, 43, 5, 53, 6, 63, 7, 73, 8, 83, 9, 93,

whenever I put P in the text, a P shows up in the corresponding cell
whenever I put a H in the text, a G shows up in the corresponding cell
whenever I put a G in the text a H shows up in the corresponding cell

there is also the potential for a letter to show up at the end of the
entered text , B, N, O or R if it is at the end of the text in the entered
cell it also has to show up in the autofilled cell.

each column is a day of the week and I decide who does what with who on each
if I enter text in cell that is not part of the above it doesn't look for a
match and leaves what I entered in the cell

thanks for any assistance you can provide on this Herman

I need to count non blank cells that match a condition.

For example i want to count number of entries in Column "B" for "ANTH 328"

ANTH 328

I have tried DCOUNTA and combination of functions. Nothing is working. Help
will be greatly appreciated.

I have a follow-up question for T Valko.

Can you offer some kind of modification, or alternative, to the second
function (below) that you gave me yesterday?

In the form that you gave all functions to me, all functions worked great!
However, I made some modifications, and now I have a problem here.

I used your first function, and ended up counting the number of matches,
between items in my Column A on the active sheet, and items in Column A on
the Import Sheet:
=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))
CSE, of course. This function is in my Column B of the active sheet, and
this works fine.

Then, I used your second function, and found all cell addresses on the
Import Sheet, which is what I wanted:

I am looking for a way to automatically copy data from one spreadsheet to
another spreadsheet.
Main Spreadsheet has a 1st column with phone number followed by several more
columns of user information.
2nd spread sheet has a different list of the same data formatted in the same
I want to combine the two spread sheets.
If the number exists on the main sheet it will copy the data row by row and
paste next to the correct number (by matching column A) and paste it next to
it, offsetting the data to start at the correct column.
2nd issue is if the number does not exist in the main spread sheet that the
macro will insert a row and add the information and the number in the first
I have a third spread sheet that will also need to find, match and paste on
the same row offset a few columns more
Is this possible?

I need to count the head to head results in a poker league. There are 7
players and the league works with points being allocated for all
places. I am interested in only the winner and second place. I want to
count all the final (tournament poker format games work on a knockout
basis until 2 players are left ot play for first place) head to head
results for each player against every other player. The spreadsheet is
set out like a table with names in the leftmost column and then each
games results in the adjoining column. Next to each name is the points
they got for that game. I need to count each time a players get 10
points and match them to 7 point name for each game. This will give me
the number of head to head wins that play has against an apponent. See
image for layout of league. I may need to do a number of interim
formulas to get the final results - thank you.

|Filename: poker league comp 1.jpg |
|Download: |

sharkh's Profile:
View this thread:

I am doing some screening and analysis on a stock database and I want to rank
each company according to certain criteria. There are approximately 5,000
companies in the database.

So for example, suppose I rank the companies first by their respective
Return on Equity. Let's say in that ranking Microsoft is number 50 of 5,000.
Next I would rank the companies by sales growth. Let's say in that ranking
Microsoft is 175 of 5,000.

What formula, commands or spreadsheet design would I use to have Excel match
all of the companies in the database with their respective scores and add the
two rankings together? For example, to find Microsoft in the two lists and
add the two rankings together for a total score of 225?

Thanks in advance for your help.

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