Free Microsoft Excel 2013 Quick Reference

Match cells in one column to another Results

Dear friends

I'm a Chinese language student who's new to this forum, so very nice to meet you! I am trying to make a multipurpose Excel program for me and my fellow students to test and consolidate our Chinese (no easy task!). My Excel knowledge is limited, and in particular, I'm not good with table manipulations.

Something that various parts of my nascent program are trying to do is interrogate a big data set of several thousand entries (my vocab entries, ordered in columns by 'topic', 'chinese', 'pronunciation', english' etc.) As the nature of Chinese is that many entries contain some of the same Chinese characters, I want to find a way to enter a character in a cell on a separate sheet, and dynamically list all the entries from my vocab list which contain that character. At the moment, the only way I can do this is by inserting another column in my list containing something like (L3)=L2+COUNTIF(K3,"*"&$M$1&"*"), such that for every new instance of an entry that meets the criteria, the value in the column at that point increases by one. I then use INDEX(MATCH(1,2,3,4,5,6 etc.)) on that column with the relevant data column I want to extract. This seems very inelegant, and is very processor intensive given the size of my data set. I would have though you could use some function involving SMALL (or something else I don't know) to do this much more efficiently. Would anyone kindly be able to help?

Many thanks!

Graham

hi all

attached is an example SS

What I want done is:

to be able to determine which route over rules another IF a price are equal to each other.

for example, Row 2 contains numbers - basically an order in which I can change and manage of the carriers I want to overrule base on least pricing.

Row 4 is a simple example, if carrier a and b match in price I want carrier b to over rule

Row 6 is another example where the highlighted cells are the more expensive but I would like Carrier G to over rule Carrier E

Row 7 is another one where all the prices are similar and I need them to be on order based on the number sequence I have on row 2

I was using an offset function to determine least cost by carrier but will only chose the first column over another column behind it

for column J-P I would like the same instance of what I discussed above. but where LCR is where i used the offset function to determine carrier but again I would it to determine which overrules who and in relation to its pricing as well

thanks

need more explanation let me know

I would like to figure a count using two criteria.

I only want to count within a column that has "1" or "2" or "3" (basically going to have three different answers in three different cells) I can get this (=SUMIF(C34:C164,1)/1)

HOWEVER.... I only want to count the ones that match if another column equals a specified range (=COUNTIF(F34:F164,"<"&"05:59"))

How do I combine the two criteria to get an answer for 1,2, or 3?

Hi guys,

I'm hoping someone can help me out ! I have 2 worksheets and what i need to
do is compare 2 cols from one to 2 cols in another - if they match then bring
across a value from an X col.
It's basically what a Vlookup does but instead of comparing one col and
brining acorss a value i want to make sure that two cols match before it
brings across a value.

So in lay terms :

If Value in cell Sheet1!A1 = a value in Sheet2!$A$500
AND Then
value in Sheet1!$B$1 = The value in cell D from the same row in which you
found the first matching value
THEN
bring across the value in cell F from that same row

Thanks guys and gals!!
Deb

I am wanting to compare 2 columns for exact matching. If there are two
matching items I want to be able to say "A match" in a chosen cell for all
the ones that match. Column C will be retrived in an random order... so how
would I write the formula for that?????

Example:
A B C
45time 11tune
A match 11tune 89time
47doog 43jkjkj
A match 123ABC 123ABC

If possible include how to highlight the ones that make as another option.
Thanks

I am in the process of creating a spreadsheet with multiple worksheets. I want to use hyperlinks to jump from data on one sheet to more detailed/related data on another sheet. This works great until some of the data is sorted. The hyperlinks refer to the original cell not with the sorted data. I then tried using the match function to find the row and indirect to get to the actual cell. This works as long as the data is only sorted on the primary column. When sorted on other columns the match comes up with #VALUE.

Example:
Sheet1
John
Bob
Don
Steve

Sheet2
Name Sales
John 5,000
Bob 4,000
Don 3,000
Steve 2,000

After sorting Sheet 2 on column a (Name) the hyperlinks won't work. Using match Sheet2 always has to be sorted on Name. Currently I am using Excel 97 and 2000. Does 2003 solve this?

Okay, I have a problem with an IF VLOOKUP statement, one that I thought would be handled by setting the range lookup to true (or leaving it blank). It does not. I want to have the formula check in a cell to see if it contains the contents of another cell. The cell it is checking may have other values in it in addition to the contents being looked up. The IF part comes in where IF the formula finds the contents within that cell, it replicates that value. If not, then it says "no". Here's a version of the formula:

=IF(VLOOKUP(A15,blist,2)=A20,A20,"no")

- A15 contains a person's name (Meg, in this case)
- blist is the table array
- 2 is the column index number I want to check
- The contents of B8 (the col_index_number cell): blee blow blah
- Leaving the range lookup blank (or set to TRUE) should find an inexact match

A20 contains a term ("Blah", in this case)
IF true, then it displays the contents of A20: "Blah"
IF false, then it displays "no"

Well, it only displays "no", unless I *only* have the value "Blah" in the cell. Any other value dumps the formula. I want the formula to look for "Blah" even if "Blee" and "Blow" are in there with it. What am I doing wrong?

I have two columns in one worksheet containing a first name and a last name:
WK1ColA WK1ColB
Billy Bob
John Doe
Jane Smith

In the same file but another worksheet I have two more columns one with the full name and another with a date:
WK2ColA WK2ColB
Billy Bob 1/15/2004
John Doe 6/21/2004
Jane Smith 7/1/2004

I want to search the entire ColA2 to find the cell that has both ColA1 and ColB1 and then return the corresponding ColB2 value for that name. None of the columns can be sorted alphabetically or numerically. I was hoping that something like this would work but it returns a general error stating that the function is wrong which I believe that the "AND" is causing the error:

=OFFSET('WorkSheet2'!$A$1, MATCH ('WorkSheet1'!A1 AND 'WorkSheet1'!B1, 'WorkSheet2'!$A$2:$C$500, 0), 1)

I am desperate for help and would appreciate any input anyone can provide. Thank you.

I've created a workbook which tracks stats for my local pool league. There are team, partner sets and player stats collected per week. I also want to track player stats on a per game basis and this is the reason for the post. I've run into a problem in that I can't copy the range of collected data for each player per game played to just one sheet for all players due to the number of rows that would be generated for the maximum players that could be involved in the league (144), the maximum number of weeks (48) and maximum number of games per player per season (240). This combined with the number of INDEX array formulas that would collect this data per player on another sheet makes the collection for each individual player run very slow for each entry. I've determined that I need to separate data on separate sheets per the week being collected. This would be a total of 48 sheets. This way, rows per sheet to be indexed will be less than 800 for each week instead of 35,000 for all weeks combined.

What I want to do, and need help doing is as follows:

The sheet named "Scorecard" is where I enter all of the stats to be collected for each match. On the Scorecard sheet there is a drop down for the week number of the current match being entered, 1 through 48. A range of data, for instance AK112:AU171, is collected from the information entered for each player within the current match on the scorecard. I want to push a button and have the macro look at the week number (Q3), check if there is already a worksheet named as "FullPlayerStatsW1" and if it doesn't exist, create it. "FullPlayerStatsW" would be the name of each sheet followed by the week numbers from 1 through 48. After creating the sheet, the macro should copy the range AK112:AU171 and paste it starting in column A row 1 of the newly created worksheet and paste all other games from other matches for that week at the first blank row found in the new sheet, A:K. All data should be pasted as values only on the FullPlayerStatsW (1-48) sheets.

Let me know if I need to clarify anything. Thanks!!

Please can somebody help!!!

Firstly I am not trying to extract free labour, I have tried my best to resolve this over the last week, searched numerous forums for the answer but to no avail.

Here goes.... I have two excel files: -

1st excel file is named 'BPC Form' of which I have a button in the top left of the form named 'Transfer Data'. The form is filled in as a (Part 1 Submission) and when clicked it selects various cells on the form, opens up my 2nd excel file named 'BPC Projects Datas' and copies the data in row format into this 2nd form.

The 'BPC Form' will at a later date be filled in with additional information known as a (Part 2 Submission). However what I would like is that when the 'Transfer Data' button is clicked this time, it looks at the Archdiocesan Job No named Arch No on the 'BPC Form', opens up the 2nd form 'BPC Projects Datas' and looks for the corresponding/matching Arhdiocesan Job No in column A and pastes the additional infromation into that row.

I have attached both files so that you can see my coding and that I have tried to make an attempt at this.
I have indicated in the coding behind the button the point at which I am stuck.

Kind Regards
Steve

I am basically trying to do the following:

Copy a value from H13 on one worksheet (User Interface) and paste the value on another worksheet (Customer) in cell "Ox", the O is column (letter) O and x is the index value I am grabbing from cell U1.

Reason I am trying this is to allow for the row number to change according to the value they select on another sheet.

I have been getting Error 400

Any suggestions?

I have tried the following.

Sub UpdateValues()
Dim offs As Byte
offs = Range("Customers!U1").Value
Range("H13").Select
Set MyRange = Selection
MyRange.Copy
Sheets("Customers").Activate
Application.Goto ActiveWorkbook.Sheets("Customers").Cells(1, 15).Offset(offs, 0)
End Sub

OR

Sub UpdateValues()
Dim ColVar As Integer
ColVar = Cells(1, 21).Value
Range("H13").Select
Set MyRange = Selection
MyRange.Copy
Sheets("Customers").Activate
Application.Goto ActiveWorkbook.Sheets("Customers").Cells(ColVar, 15)
End Sub

Sheets("User Interface").Activate
If Range("AA6").Text = "TRUE" Then
Range("H13").Select
Selection.Copy
Sheets("Customers").Activate
Range("Customers!O1").Offset(Range("'user interface'!u1").Value - 1).Paste
Else
Range("E19").Select
End If
End Sub

Hi all,

I am arranging some data and have a question that is puzzling me.

First, I have a pair of latitude and longitude located in cell M2 and N2 respectively.
Second, I have the whole list of pairs of coordinates for all locations in column R and S.
Third, I have unique code for each location in column P. For example, the pair of coordinates in R4 and S4 correspond to the unique id in P4.

My task is to find the one pair of coordinates in R and S that matches up with M2 and N2. After that, I need to paste the corresponding unique id in P onto cell O2.

For example, if R421 and S421 matches up with M2 and N2, I will then paste P421 onto O2.

Some websites suggested using vlookup but I wasn't able to use this command to compare two columns simultaneously.

Any suggestions are welcome! Thanks!

I have a fairly static data page (info is cut and pasted quarterly) and a summary page.

On the summary page, I need data summed by multiple criteria. Each transaction includes the date, salesrep, account, and amount. I need to filter by date, have 3 accounts sum in one place, and 2 in another, and cross reference this by salesrep.

This is part of a much larger sheet, so a pivot table won't quite work, and I can accomplish this using sumproduct, but I would need to repeat the below equation nearly 9 times in a single cell (3 times for each account) - making it quite long and cumbersome.

=SUMPRODUCT(--(invoices!$A$6:$A$2000>=Summary!$B$2),--(invoices!$A$6:$A$2000<=Summary!$B$3),--(invoices!$L$6:$L$2000=Summary!A6),--(invoices!$C$6:$C$2000="Fee income : Quarterly Fee"),invoices!$N$6:$N$2000)

I'm looking for a UDF to complete the task. I got started below with defining some variables and ranges, but I'm not sure where to go next.

Basically, I'll put this equation next to a cell with the sales rep's name, and it will need to find transactions that are between two dates (the first two conditions in the above sumproduct)have the sales rep's name on themmatch one of three different accountsif the above conditions are met, go ahead and sum the number in the amount column.

One note - there are a few places on the transaction where a rep's name and amount are listed.

Thanks in advance.

Function
recurrings(salesrep As String)

Dim dateclosed As Range, account As Range, amount As Range
Set dateclosed = Sheets("invoices").Range("a6:a2000")
Set account = Sheets("invoices").Range("c6:c2000")
Set amount = Sheets("invoices").Range("h6:h6000")


The macro below is SUPPOSED to run an autofilter on 3 columns, and if there ARE results, copy those to another spreadsheet. If there AREN'T any results, it's supposed to paste a message on that other spreadsheet. What's happening is that whether results or found or not, it's pasting the message and NOT the results. The column headings are in Row 1, so I'm looking to see if anything shows up in row 2 and below. I'd appreciate any help I could get on this.

Sub test4()
Dim DstWkb As Workbook
  Dim rng As Range
  Dim RngEnd As Range
  Dim Cell As Range
  Dim rng2 As Range
  Set rng = Selection
      
  Set DstWkb = Workbooks("Active Position Checklist.xls")
'Restrain the filter to cells from A1 to the last entry in column X
          With DstWkb.Worksheets("APRData")
            Set rng = .Range("A1:X1")
            Set RngEnd = .Cells(Rows.Count, rng.Column).End(xlUp)
            Set rng = IIf(RngEnd.Row < rng.Row, rng, .Range(rng, RngEnd))
          End With
                         
          'Filter the data using column H and I (for vacancies) and G or E (for "No Placement" or "Remain in
Place")
          rng.EntireRow.Autofilter Field:=8, Criteria1:="="
          rng.EntireRow.Autofilter Field:=9, Criteria1:="="
          rng.EntireRow.Autofilter Field:=19, Criteria1:="=G", Operator:=xlOr, _
        Criteria2:="=E"
          'Trap the error if there were no matches
          On Error Resume Next
                             
          'See whether there's data or not
         Set rng2 = Range("A2:X" & Rows.Count).SpecialCells(xlCellTypeFormulas)
          If rng2 Is Nothing Then
            DstWkb.Worksheets("Report").Range("A65536").End(xlUp).Offset(1, 0) = "Wow!  No 'No
Placement' or 'Remain in Place' vacancies! That makes it nice."
        Else
           'Copy and paste only the filtered data
           rng.SpecialCells(xlCellTypeVisible).copy _
                Destination:=DstWkb.Worksheets("Report").Range("A65536").End(xlUp).Offset(1, 0)
         End If
         'Clear the error if there was one
          Err.Clear
         'Return error control back to the system
          On Error GoTo 0
                           
          'Copy and paste the Solution
            Sheets("Sheet1").Range("A257").EntireRow.copy
Destination:=Sheets("Report").Range("A65536").End(xlUp).Offset(1, 0)
    
          'turn off the autofilter
          Worksheets("APRData").AutoFilterMode = False
          

    Set rng = Nothing
    Set DstWkb = Nothing
    Set RngEnd = Nothing
        
End Sub


Hi all,

I have a task that I need to perform every now and then. And even though I normally find my ways around in Excel, I can't find a working solution for this one.

I have a list of features that I need to match with another list and place them on the same rows in my sheet. The attached file contains 2 worksheets where "Before" shows my normal input, and "Wanted" shows what I need to do.

Cols A-E contains info for the first set of features, cols G and H is the cells I want to move. Please note that sometimes the G and H columns contains info that are not present in A-E, and then I need to move those features around as well. An example is present at the bottom of the "Wanted" sheet.

I would really appreciate some help as this is taking me forever...

Hi, trying to do a Macro doing the following:

1- I have two Excel files, #1 with a list of group (column A) (12345, 23456...) and #2 with a new list of group with their associated Member Group (100, 027...). This second file has two tab. Tab #2.1 is the Group (column A) and Member Group (column B) of one system and tab #2.2 is Group (column A) and Member Group (column B) for a second system (we are converting one system to the other)

2- First, I would like to highlight in color the value in column A from files #2 tab #2.1 that are in the list of group (column A) of file #1;

3- Secondly, highlight the column B of file #2 tab #2.1 when a match is found in file #2 tab #2.2 for column A and B. In other word, highlight the Member Group in the first list of group and member group when the equivalent is found is the second list of group and member group;

4- This last request is not necessary but I would like to know if possible, how to do it. Could column A and B of tab 2.1 of file #2 of the highlighted cell be copied into another tab. This way we wouldn't have to sort throught all the data (10K+ record).

I look but didn't find according to my need. I can't post file since not at work today but if absolutely necessary please tell me and I'll do it.

Thanks in advance for the help! Looking forward for a VBA training.

Leith Ross was very helpful getting me to this point, but I have some remaining questions, so I thought I'd try reposting.

For the attached spreadsheet ModelRequirementBreakdown2a.xls, I want to be able to make selections from within combo boxes in the Product column, not only by using the combo box dropdown and select mechanism, but also, if possible, by pasting a cell range whose data matches one of the existing selections available in the combobox definition.

In other words, if I have a single column cell range from somewhere with the following data in 2 rows:
VNXES
HDX

I want to be able to paste that range starting at B8, such that it makes the combobox selections in B8 and B9, and thereby populates rows 8 & 9 with the appropriate data (in exactly the same way as making these selections manually with the drop-down would).

Is this do-able?

I saw an example of another approach to data selection which I liked somewhat, but I don't think it will work. I thought I was good at figuring out things, but I can't crack this simple one.

In the attached spreadsheet NamesReference.xls, cell A1 of Initial Quality worksheet has a drop down that apparently references the Namelist? "FieldeList" which is on the List worksheet, cells A1-A14.

By examining A1 of Initial Quality, how can you determine that this reference exists?

How do you establish such references to Namelists, with the drop-down arrows?

I like this method of using drop-down lists better than ActiveX combo boxes, because once you've chosen what you want, the appearance is cleaner. Big problem however, is if you try to paste directly into the A1 cell, it doesn't select the item out of the namelist, it overwrites the cell, erasing the namelist functionality. Am I missing something?

Thanks,

David

First off: This forum has been VERY helpful for me and I have learned way more than I thought I ever would about Excel and VB. I have automated a few spreadsheets we work with at my current job with a little help from the search function here.

Now onto my current mess. I spent most of yesterday evening helping someone automate a spreadsheet with MANY different IF:Then scenarios which are triggered by dates, dollar amounts, and the period of contracts. The spreadsheet allocates dollar amounts across our fiscal quarters beginning with the month following the provided date. The amount allocated is provided in another column and divided by the length of a contract which is shown in a separate column.

I spent several hours (and I'm sure I used way more lines of code than necessary) and have working macros for the first row of the spreadsheet. There are 100 more rows below this one where I need the same macro to occur. There has to be an easier way than copying and pasting the current formulas and changing all the cell values mentioned to match the next row. Any suggestions???

Appreciate any help you can provide...

Greetings

On sheet1 I have a table in A1:B20 that shows 'acceptable pairs', like this:

a mon
a tue
a sun
b mon
b wed
c mon
c thu
c fri
c sat ...etc

On sheet2, users enter similar values in columns C and D. If users enter
'acceptable pairs' as listed in sheet1, that's OK, but if they enter, say
b tue, I don't want excel to accept the pair.

What's the best way to do this?

much thanks for any ideas

Giselle

Hello all,

I'm trying to write up a macro for excel to streamline some things I
have to do regularly, but this one is giving me a bit of trouble. I
don't think it is too complicated, I could probably do it in C++
easily, but VBA is always iffy with me. Luckily, with some help from others, I got most of it written but I am having a small problem still.

First some background:
I have two workbooks, each with 1 sheet in them.

'WorkbookA.xls|Sheet1' is more or less static list but it may have
additional entries from month to month (think of it as a 'flag it'
list). It essentially contains information about things. The names are
all in column A (lets say down to 100 for simplicity's sake). The
locations are in column B.

Example:
1) Column A		Column B

2) Person A            	CHARLOTTE
3) Person B              JACKSONVILLE
4) Person C              HALIFAX
'WorkbookB.xls|Sheet1' has a rather large list of transactions which
get assigned an ID in column A by a macro we already have in place. The
names are in column C. The locations are in column D

Example:
1) A         B                  C                  D


2) 123     Contact A	Person A        CHARLOTTE  (More info)
3) 123     Contact A       Person B        JACKSONVILLE (More info)
4) 225     Contact B       Person A        CHARLOTTE (More info)
5) 225     Contact B       Person D        LOS ANGELES (More info)
6) 334     Contact C       Person C        HALIFAX (More info)
However, Workbook B does not account for any of the 'flagged' items in
workbook A. In the above Workbook B example, rows 2,3,4, and 6 should be flagged.

So I need this macro to take the data from column A in the
first workbook, and run it through Workbook B (column C). If it were to
find a match, it should change Workbook B (Column A) to another ID value (lets say
5000).
For simplicity, only name matching was done at first, here is the code thus far:
Dim rngA as Range, rngB as Range, rng as Range 
Dim cell as Range, sAddr as String 
with workbooks("WorkbookA.xls").Worksheets("Sheet1") 
  set rngA = .range(.Cells(1,1),.Cells(1,1).end(xldown)) 
End with 
With workbooks("WorkbookB.xls").Worksheets("Sheet1") 
  set rngB = .Range(.Cells(1,3),.Cells(1,3).End(xldown)) 
End with 
for each cell in rngA 
   set rng = rngB.Find(What:=cell.Value, _ 
       After:=rngB(rngB.count), _ 
       LookIn:=xlValues, _ 
       LookAt:=xlWhole, _ 
       SearchOrder:=xlByRows, _ 
       SearchDirection:=xlNext, _ 
       MatchCase:=False) 
   if not rng is nothing then 
      sAddr = rng 
   do 
      if rng.offset(0,1).Value = _ 
         cell.offset(0,1).Value then 
          cell.offset(0,-2).Value = 5000 
      end if 
      set rng = rngB.FindNext(rng) 
    loop until rng.Address = sAddr 
  End if 
 Next
I get errors on the following line:
cell.offset(0,-2).Value = 5000

Any ideas?

Thanks for your help.


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