Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Select items based on criteria and return in an array

I'd like to select items in a way like Sumifs or Countifs does and then return all of those to a function.

So, assuming SomeIFS acts like Countifs (but returns the values and doesn't do a count) I'd end up with: =Somefunction(SomeIFS(A1:B10,">10"))

Is this possible?

Thanks,
Al


Post your answer or comment

comments powered by Disqus
New user seeks help!

I need some assistance in selecting rows based on cell criteria. Here’s a simplified example of my data:

A1:A5 BOB, BOB, CLIVE, FRANK, FRANK.
B1:C4 HOME, (BLANK), HOME, HOME, (BLANK).

I’m trying to find a way of automatically selecting (and ultimately deleting) the rows where the data in Row A is duplicated and the adjacent right cell is blank. Rows 2 and 5 would be selected in this example.

Can’t seem to crack it myself. Any help would be greatly appreciated.

Thanks,

Jonno.

I have two Workbooks opened, Workbook_1.xlsx and Workbook_2.xlsx

In need to transfer data from Workbook_1 to Workbook_2 (next blank row), based on a criteria. In the example below:

Value to transfer: ID
Criteria to transfer data: Tested (Y), Transferred (Blank)
Hence in this example, ID PQ93 should be transfer to Workbook_2, next blank row.

Workbook_1
ID       Tested   Transferred
AB12       Y          T
DO32       N
PQ93       Y
Workbook_1
S/N     ID
01      CD02
02      JK84
I wrote a code, but keep getting error at Cells(x, 2).Value = ColA(i)

Sub TransferData()
    Dim VLast1 As Integer
    Dim VLast2 As Integer
    
    Workbooks("Workbook_1").Activate
    VLast1 = Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim ColA() As String
    Dim i As Integer
    
    i = 1
    For x = 2 To VLast1
        If UCase(Cells(x, 3).Value) <> "T" Then
            If UCase(Cells(x, 2).Value) = "Y" Then
                ColA(i) = Cells(x, 1).Value
                i = i + 1
            End If
        End If
    Next x
    
    Workbooks("Workbook_2").Activate
    VLast2 = Cells(Rows.Count, "B").End(xlUp).Row + 1
    i = 1
    For x = VLast2 To VLast1
        Cells(x, 2).Value = ColA(i)
        i = i + 1
    Next x
End Sub
Any Idea how to solve this???

Hello,

I have two Worksheets Sheet1 and Sheet2.
I want to automatically fill up the Sheet2 with the records copied from the
Sheet1 with selected columns.

For example, Sheet1 looks like following:
A B C D
R1 1 3 1 1
R2 2 2 2 3
R3 1 1 2 1
R4 4 3 5 4

Now blank Sheet2(to be automatically populated) looks like following:
A B
R1
R2
R3
R4

What I want is that Records from the Sheet1 should be selected based on
criteria and Sheet2 to be filled automatically with selected columns.

Example:
Records should be searched in Sheet1 where if a row is having column B's or
column C's value as "2", then Sheet1's respective row's columns to be
populated in Sheet2's column(let us suppose only cloumn A and B) for every
qualifying record, And I should get Row2 and Row3(which matches this
condition) as records in Sheet2 populated.

How can I get this, or which formula approach I can opt?

Thank You,
Saurabh Khanna.

I'm a newbie in need of some experienced programmers here.

I am wanting to create a macro that will select rows based on a TRUE value in column B of my spreadsheet. I know this is probuly very easy, but i'm new to this and have been running around in circles for the last couple of days. Attached is a view of what the first few rows of my spreadsheet look like.

My final intent is to insert all rows that contain the TRUE value into a report on Sheet2.

I can handle the programming of the copying and pasting into certain areas, but so far i have been unsuccessfull at being able to select the rows.

Thank you in advance for helping me..
-James

I need to count the number of nonblank cells in one column based on criteria
from cells in another column. Example: Column A contains years 2006, 2007,
2008. Column B contains rank such as Lt., Col., etc. I need to know the
total number of 2006 individuals who have the rank of Lt., the number of 2006
individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have it
look at the other column for the type of count I need.

hi,

I would like to get help creating invoice in excel based on data and
figures in packing list (excel format). In packing list I have
quantities etc. info.

This packing list is sent to office where our invoicing person is
creating invoice based on packing list data.

Other invoice data, such as pricing, is coming from other sheet =
there is 2 workbooks to combine in one invoice sheet.

packing list.xls
price.xls (includes price & product info sheet and customer sheet)

Any suggestions ? I am willing to compensate for a working solution.

Cheers!

Hi Everyone,

Does anyone have a VB macro that will copy information from a cell (this will be variable based on criteria) and paste it into the middle of another cell that contains HTML code?

The criteria for the copy/insert/paste is that the information in the cell that is to be copy/inserted must match up with the correct number (part number).

I hope this makes sense. I have attached an example spreadsheet. (Pictures are worth 1000 words. ) Hope someone can help. I checked this site and couldn't find anything that was solved that matched the specifics.

Thank you in advance.

Hi,

I’m looking for a way to average a range of data based on criteria. I’ve attached an example worksheet (2007).

The result being that if the formula looked for “Test1” then it would return 25.7 (as the blue highlighted cells average 27.5).

I tried the below (inputting with CSE) but for some reason it gives me 9.7?!



I am looking for the proper formula/VBA code to find the MIN/MAX based upon the starting two numbers in an array.

Example:
Array...

25123
26123
25987
26987

I want to get the MIN/MAX for all values that begin with 25. I feel that I am close with my formula, but I just can't seem to pull out the correct number.

Any help would be appreciated. Thank you.

I have two sheets. Sheet1 has multiple lines of data. Based on criteria
chosen in sheet2, I need to display in sheet2 all of the rows of data that
match the criteria. I'm having trouble with multiple criteria (i.e. for
name1, I want all matching rows of data in a certain timeframe. Not all data
in ColA is are same name and will not be in any particular order.

Sheet1
A B C D E F G H I
Bryan D Smith 2/1/06 1 2 2 6 6 1
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

Sheet 2 would return based on user selecting between dates of 3/1/06 & 3/15/06
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

I can get it to return all of the items for that match in column A but how
do I for certain dates?

Here is my formula that I'm using to get all rows that match

{=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data
entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}

Thanks in advance for any help!

I have two sheets. Sheet1 has multiple lines of data. Based on criteria
chosen in sheet2, I need to display in sheet2 all of the rows of data that
match the criteria. I'm having trouble with multiple criteria (i.e. for
name1, I want all matching rows of data in a certain timeframe. Not all data
in ColA is are same name and will not be in any particular order.

Sheet1
A B C D E F G H I
Bryan D Smith 2/1/06 1 2 2 6 6 1
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

Sheet 2 would return based on user selecting between dates of 3/1/06 & 3/15/06
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

I can get it to return all of the items for that match in column A but how
do I for certain dates?

Here is my formula that I'm using to get all rows that match

{=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data
entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}

Thanks in advance for any help!

NEED HELP ON RETURNING THE TEXT VALUES BASED ON CRITERIA.

BELOW I MARKED THE VALUES IN TWO COLUMNS 'ACTION ITEM' AND 'STATUS'.

NOW I WANT TO RETURN ALL THE 'ACTION ITEM' WITH THE STATUS 'PENDING' TO OTHER SHEET OR CELL, THIS QUERY WILL ME TO TRACK THE ACTION ITEMS DAY TO DAY.

ACTION ITEMS STATUS
------------------- --------------------
MEETING - PENDING
REPORT - DONE
METRICS - PENDING

Hi, i am not sure what to search for. I need a formula that will return text based on criteria that i set.

Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points

Thanks

edit: the "sumifs" functions is in the direction that i am thinking, but it doesn't do the text return

Hi,

I'm trying to find the minimum value in a range (C16:C20), but I only want to consider values based on criteria in range (D16:D20). For example:

C16=10, c17=22, c18=25,c19=4,C20=76.
D16="x", D17, D18, and D20 are blank, and D19 = "x".

In cell D1, I want to put the least value from column C based on where I have an "x" in column D. In this example, because I have an "x" in D16 and D19, I only want to consider the values in C16 and C19. So in this example, D1 would equal 4.

Same thing in D2 except I would want the maximum. Considering only C16 and C19, D2 would equal 10.

I'm hoping to use a formula in cells D1 and D2 rather than VBA. I can sum them using "sumif" with the formula =sumif(D16:D20,"x",C16:C20), but can't figure out how to take the least and maximum.

Any help would be greatly appreciated.

Thank you.
Steve

Need to return a sum of values based on criteria from another file.

I have two files, each file has a unique text ID in column A, in column B of
one file is a sales figure in the other file, column B contains a debt
figure. The files are generated by a system each month automatically and I
can't get the producer to provide a file with sales and debts in one file, I
also don't want to insert a column in one of the above files and add a look
up to the other.

So far, I have a simple formula in a separate file
=SUMIF([Book3]Sheet1!$B:$B,"

I would like to print the worksheets in a workbook based upon whether the
user has entered data on that sheet. So in a 5 page workbook, depending on
the data entered, the workbook will print (selected worksheets based on this
criteria) pages 1,2,5 or 1,3,4,5 etc. Excel will print selected sheets, I
just need to have these sheets selected based upon cell entry(data present on
a particular sheet). I looked thru the formulas, and considered a macro, but
I couldn't find a criteria to base the macro on.
Any ideas?

Thanks in advance to all.

My apologies for the vague Title in my original post.

I've have the following code:

Function
IsOdd(x As Integer) As Boolean
    IsOdd = (x Mod 2) <> 0
End Function
Sub ImportTravelTime()

    Dim xlBook As Workbook
    Dim xlSheet As Worksheet
    Dim Continue As Boolean
    Dim OutSh As Worksheet
    Dim i As Integer
    Dim j As Integer
    Dim From As Integer
    Dim To1 As Integer

    Set OutSh = ThisWorkbook.Sheets("Travel Time")
    Range("C10:G14,C21:G25,F9,F20").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("B6:B15,B17:B26,C6:J8,C17:J19").Select
    Selection.ClearContents
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Continue = False
    For x = 1 To Excel.Workbooks.Count
      If LCase(Excel.Workbooks(x).Name) = "tt.xls" Then
        Continue = True
        Set xlBook = Excel.Workbooks(x)
      End If
    Next x
    
    If Continue Then
      xlBook.Activate
      Sheets("Travel Time Results").Activate
      Range("A2:C3,A58:C59,E2:F2,J2:L4").Select
      Selection.UnMerge

      OutSh.Range("B6").Value = Range("A2").Value
      OutSh.Range("B17").Value = Range("A58").Value
      OutSh.Range("C6").Value = Range("J2").Value
      OutSh.Range("C17").Value = Range("J2").Value
      OutSh.Range("F9").Value = Range("E2").Value
      OutSh.Range("F20").Value = Range("E2").Value
      
      OutSh.Activate
      Range("B6:B15,B17:B26").Select
      Application.CutCopyMode = False
      With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
      End With
      Range("C6:J8,C17:J19").Select
      With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
      End With
      xlBook.Activate
      Sheets("Travel Time Results").Activate
      Range("A2:C3,A58:C59,E2:F2,J2:L4").Select
      Selection.Merge
      
      Sheets("Travel Time Input").Activate
      For i = 1 To 10
          Set findit = Range("A:A").Find(what:=i)
          If Not findit Is Nothing Then
          firstadd = findit.Address
            If IsOdd(i) Then
                outrow = OutSh.Cells(Rows.Count, 1).End(xlUp).Offset(Application.WorksheetFunction.RoundUp(i / 2, 0) + 8,
0).Row
                OutSh.Cells(outrow, 3).Value = findit.Offset(0, 2).Value
            Else
                outrow = OutSh.Cells(Rows.Count, 1).End(xlUp).Offset(Application.WorksheetFunction.RoundUp(i / 2, 0) + 8,
0).Row
                OutSh.Cells(outrow, 4).Value = findit.Offset(0, 3).Value
            End If
          End If
      Next i
      
      Sheets("Travel Time Input").Activate
      For j = 1 To 10
          Set findit = Range("G:G").Find(what:=j)
          If Not findit Is Nothing Then
          firstadd = findit.Address
            If IsOdd(j) Then
                outrow = OutSh.Cells(Rows.Count, 1).End(xlUp).Offset(Application.WorksheetFunction.RoundUp(j / 2, 0) + 19,
0).Row
                OutSh.Cells(outrow, 3).Value = findit.Offset(0, 2).Value
            Else
                outrow = OutSh.Cells(Rows.Count, 1).End(xlUp).Offset(Application.WorksheetFunction.RoundUp(j / 2, 0) + 19,
0).Row
                OutSh.Cells(outrow, 4).Value = findit.Offset(0, 3).Value
            End If
          End If
      Next j
      
Stop 'This is where I'm having troubles figuring out the sum range
            
      Set From = OutSh.Range("C10")
      Set To1 = OutSh.Range("D10")
      Sheets("Travel Time Results").Activate
      


    Else
        MsgBox "Travel Time.xls is not Open"
    End If
  
End Sub
Here is what I'm trying to accomplish after the Stop in the above code (BTW, I run the code from VPP.xls, worksheet Travel Time).

Based on the Street name in VPP.xls, worksheet Travel Time, I want to find the distance between the streets from TT.xls worksheet, Travel Time Results, and report that in column E (the table is filled out how it should look after the code is run).

For example, I want the distance between Street A and Street F WB. It should return a value of 5.2 (5238.9/1000).

I've been using the following excel formula in column E in VPP.xls, but I want the code to actually just report the value in the cell. I can't figure out how to use the application.worksheet function, which I may not be able to do because of the : contained within the formula.

Similar to finding the distance, I want to find the A and B times between those same streets and put that in the appropriate
A and B columns.  I imagine that would be similar code to the above, so if I can just figure that part out, I can get the
rest.

Thanks

J

Again, I apologize for my vagueness originally.

Hi,

I have a query in excel regarding the extraction of certain specific rows based on a criteria. It goes like this:

I have two sheets in one excel file. The first excel sheet has five columns: Name, number, date, type and reg - with approx 8000 rows.

The second excel sheet has just one column: 'Name' with around 160 rows.

My Requirement: I want to scroll through each value in the second sheet(which is a text), check whether that particular name is present under the Name column in sheet 1, and if it is present, copy the entire row to a new Sheet: Sheet 3.

I need to get this done for all the 160 records from sheet 2 and cross check it with all the 8000 records of sheet 1 and thus get the output.

Hope I have made myself clear.

Thanks.

I have a summary page where you enter item numbers followed by the item name. I have a worksheet used as a template which I named template >.<. I have a command button that when pressed will make a new sheet copied from the template and name the sheet according to the item. My question is, how can I modify this code to copy from different templates based on criteria that I input next to the item. Say I have Item 1 in A1, Item Name in A2 and Item type in A3. Right now the macro will name the worksheet depending on A2 but will just copy the template worksheet. One more thing, how do I change this code so that I can copy from hidden worksheets? I'm new here so I hope I posted this right.

Option Explicit

Private Sub CreateSheets_Click()
Dim strCol As String
Dim strRow As String
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim strWsName As String
Dim strSrcName As String

On Error GoTo ErrHnd

'setup column letter and first row number containing names
'column
strCol = "Q"
'row (number is in double quotes)
strRow = "7"

'turn off screen updating to stop flicker & increase speed
Application.ScreenUpdating = False
            
'save this worksheet's name, so we can go back to it later
strSrcName = ActiveSheet.Name

'set start of data in selected column
Set rngStart = ActiveSheet.Range(strCol & strRow)
'find end of data in selected column
Set rngEnd = ActiveSheet.Range(strCol & CStr(Application.Rows.Count)) _
            .End(xlUp)

'loop through cells in used range
For Each rngCell In ActiveSheet.Range(rngStart, rngEnd)
    'ignore empty cells in range
    If rngCell.Text <> "" Then
        'get worksheet name
        strWsName = rngCell.Text
        'test if worksheet exists
        On Error Resume Next
         If Worksheets(strWsName) Is Nothing Then
            'worksheet does not exist
            'reinstate error handling
            On Error GoTo ErrHnd
            'copy worksheet named "Template"
            Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
            'name new sheet
            Worksheets(Worksheets.Count).Name = strWsName
            Else
            'worksheet already exists
            'reinstate error handling
            On Error GoTo ErrHnd
        End If
    End If
Next rngCell

'go back to the source worksheet
Worksheets(strSrcName).Activate

'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'go back to the source worksheet
Worksheets(strSrcName).Activate
'reinstate screen updating
Application.ScreenUpdating = True

End Sub


Hi,

I have a stock data in sheet 1 as follows:

Last Done
Market Cap (M)
PE
Price/NAV
High, 52 Wks
Low, 52 Wks
Revenue Growth
Current Ratio
Debt to Equity
ROA

In sheet 2, I have set a parameter table (cell c3 to c12) to enter a range of criteria. Based on these range of criteria entered in parameter table, I would like to populate all relevant stocks in cell b15 downwards and their respective data in cell b15 to l15 downwards in sheet 2 from sheet 1 that satisfies the criteria in the parameter table.

Is there a way to create a formula to achieve this?

Appreciate all help.

I have a cross post here:

http://www.mrexcel.com/forum/showthread.php?t=605515
http://www.excelforum.com/excel-work...-populate.html

Well I tried to upload my workbook which failed (kept getting a little red exclamation mark), tried uploading a picture of the two worksheets which also failed. I will do my best to explain the old fashion way but would be more than glad to email my workbook as that seems the only way possible (or let me know how to successfully load it up here). Essentially my goal is as follows:

I have two worksheets; one called "Dashboard" (where I want the user to enter a start and end date, and select which column he/she is interested in summarizing (preferably through a drop down).

The other worksheet "Data" contains accident/injury data. Column A is the injury date column with dates formatted as such: 1/18/2002. The rest of the columns are more information regarding the incident/injury. These currently include "Accident Cause", "Injury Type", "Description", and "Body_Part". these are contained in columns B, C, D, and E respectively. There does exist the possibility for columns of other incident/injury data to be added or for current ones to be removed (which is why if possible I'd like the drop down selection to automatically populate with the headers of each column).

Comment for "Column to Summarize":
Would like to use a drop down to select which column to select the range from. Goal is to have the macro select only the cells which correspond to dates inbetween (and including) the start and end date (date column is column A on the "Data" worksheet.

Idealy (if possibe) I'd like the macro to automatically populate the drop down menu with all of the posible column headers (Accident Cause, Injury Type, Description, Body_Part) in case any additional data fields are added to the worksheet.

Comment for "Example Selected Range Based on user defined start/end dates and column":
Example of what range the macro should select based on the example user input:

Start Date: 1/19/2002
End Date: 2/19/2002

*Column to Summarize: Accident Cause
*Idealy would like to use a drop down which is automatically populated in case new fields are added to the worksheet.

1/19/2002 Texting 1/20/2002 Texting 1/21/2002 Texting 1/22/2002 Texting 1/23/2002 Texting 1/24/2002 Drunk Driving 1/25/2002 Drunk Driving 1/26/2002 Drunk Driving 1/27/2002 Drunk Driving 1/28/2002 Drunk Driving 1/29/2002 Fatigue 1/30/2002 Drunk Driving 1/31/2002 Texting 2/1/2002 Texting 2/2/2002 Texting 2/3/2002 Texting 2/4/2002 Texting 2/5/2002 Aggressive Driving 2/6/2002 Texting 2/7/2002 Fatigue 2/8/2002 Fatigue 2/9/2002 Aggressive Driving 2/10/2002 Texting 2/11/2002 Aggressive Driving 2/12/2002 Texting 2/13/2002 Snow 2/14/2002 Texting 2/15/2002 Snow 2/16/2002 Texting 2/17/2002 Texting 2/18/2002 Ice 2/19/2002 Ice

Book1.xls
Hi,

I am pretty new to vba, and am looking for a code to cut and paste a row of information from sheet1 to sheet2 based on criteria from sheet1 cell showing "complete".
I have attached a sample of the sort of thing I am after, and am hoping for some much needed help.

Many Thanks in advance,

Tricia

Goodmorning

I am new to the whole vba thing and i am not great at trying to make code apply to my situation - my apologies for my ignorance

I am have a spread sheet (sheet 1) with a number of columns and what i am looking to do is see all the people that are participating in a certain stage in the process that is not common, So what i am wanting to do is copy the names and the corresponding number of these people (on sheet1) over to a new worksheet(sheet2) based on a yes or no criteria further on in the spread sheet(sheet1). sheet 2 has additional columns that the workers here would need to fill in.

The criteria is in sheet 1 cells Y2:Y2000
The number is in sheet 1 cells D2:d2000
The names are in sheet 1 cells E2:e2000

If its at all possible i would also like to then make the names of those people on sheet 1 a hyperlink to the additional information

Can anyone help me?

How kann I select range base on lastrow
and on last cell in row 8 where Interior.ColorIndex = 41 (blue) and some text in it?


	VB:
	
 test() 
    lastrow = Range("B65536").End(xlUp).Row 
    Dim MyArea As Object 
     
    Set MyArea = Range("B7" & ":I" & lastrow) '


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