Extract a unique distinct list by matching items that meet a criterion in excel

Filed in Excel on Sep.01, 2010

Anura asks:

Is it possible to extend this by matching items that meet a criteria?

I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a report for each Branch, so what I want is to extract only those people who match the Branch name. For example:

Frank Branch A
Frank Branch A
Frank Branch A
Joe Branch A
Mary Branch B
Jane Branch C
Mike Branch A
Joe Branch A
Dave Branch C

I would like a list of only those people for Branch A, and then be able to summarise the transactions. Or should I do this in two stages?

Answer:

Type a branch in cell F1.

Array formula in E8:

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0), COLUMN(A1)) + CTRL + SHIFT + ENTER. Copy cell E8 and paste it down as far as needed. Copy cells and paste into cell range F8 and down as far as needed.

Formula in G8:

=SUMPRODUCT($C$2:$C$11, --($B$2:$B$11=$F$1), --(E8=$A$2:$A$11)) + Enter. Copy cell G8 and paste it down as far as needed.

Next blog post is about how to accomplish this by creating a pivot table.

Download excel sample file for this tutorial.

unique distinct list matching criteria.xls
(Excel 97-2003 Workbook *.xls)

Previous articles:

Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
Filter unique distinct values where adjacent cells contain search string in excel
Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
Excel – Unique and duplicate values

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

Share and Enjoy:
  • Share/Bookmark

Comments (2)

Related posts:

  1. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
  2. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
  3. Validate unique distinct list in excel
  4. Unique distinct list sorted based on occurrance in a column in excel
  5. Extract unique distinct numbers from closed workbook in excel (formula)
  6. Create a unique distinct sorted list containing both numbers text removing blanks in excel
  7. Extract all rows from a range that meet criteria in one column in excel
  8. Extract cell values in a range using a criterion in excel
  9. Create unique distinct list sorted based on text length using array formula in excel
  10. Filter unique text values using “begins with” criterion in a range in excel

2 Responses to “Extract a unique distinct list by matching items that meet a criterion in excel”

  1. Anura Says:
    September 2nd, 2010 at 8:14 am

    This really is brilliant. Can I ask for one modification? How can I count the number of transactions per person?

  2. Oscar Says:
    September 3rd, 2010 at 11:13 am

    Anura,

    Formula in H8:
    =SUMPRODUCT(--(E8=$A$2:$A$11)) + ENTER.

Leave a Reply



Extract dates from a cell block schedule in excel, part 3

Filed in Excel on Aug.31, 2010

In this post we are going to extract all "not empty" dates in a simple calendar. In a previous blog post we extracted only date ranges and names.

Here is a picture of the calendar.

Here is a picture of all extracted dates and corresponding "names".

Array formula in cell A2:

=SMALL(IF(Sheet1!$C$5:$AG$16<>"", DATE(Sheet1!$F$2, ROW(Sheet1!$C$5:$AG$16) -MIN(ROW(Sheet1!$C$5:$AG$16))+1, Sheet1!$C$4:$AG$4), ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell A2 and paste it down as far as needed.

Array formula in cell B2:

=INDEX(Sheet1!$C$5:$AG$16, SMALL(IF(Sheet1!$C$5:$AG$16<>"", ROW(Sheet1!$C$5:$AG$16) - MIN(ROW(Sheet1!$C$5:$AG$16))+1, ""), ROW(A1)), DAY(SMALL(IF(Sheet1!$C$5:$AG$16<>"", DATE(Sheet1!$F$2, ROW(Sheet1!$C$5:$AG$16) - MIN(ROW(Sheet1!$C$5:$AG$16))+1, Sheet1!$C$4:$AG$4), ""), ROW(A1)))) + CTRL + SHIFT + ENTER. Copy cell B2 and paste it down as far as needed.

Download excel sample file for this tutorial.

Extract-all-dates-and-name-from-yearly-block-schedule.xls
(Excel 97-2003 Workbook *.xls)

Previous articles about cell blocks:

Extract dates from a cell block schedule in excel, part 2
Extract dates from a cell block schedule in excel
Count cell blocks in excel
Count overlapping dates in excel

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference


INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code

Array formula in cell A2:

Share and Enjoy:
  • Share/Bookmark

Leave a Comment

Related posts:

  1. Extract dates from a cell block schedule in excel, part 2
  2. Extract dates from a cell block schedule in excel
  3. Schedule recurring events in a weekly schedule in excel
  4. Extract dates and adjacent value in a range using a date critera in excel
  5. Populate cells dynamically in a weekly schedule in excel
  6. Weekly schedule template in excel
  7. Find empty hours in a weekly schedule in excel
  8. Schedule recurring expenses in a calendar in excel (Personal Finance)
  9. Setting up your work hours in a weekly schedule in excel
  10. Highlight specific time ranges in a weekly schedule in excel

Leave a Reply



Extract dates from a cell block schedule in excel, part 2

Filed in Excel on Aug.30, 2010

The obvious question from yesterdays blog post is: How to customize formula to extract dates for all months in calendar?

(I have added more data to the calendar since yesterday)

Answer:

Array formula in A2:

=SMALL(IF((Sheet1!$C$5:$AG$16<>Sheet1!$B$5:$AF$16)*(Sheet1!$C$5:$AG$16<>""), DATE(Sheet1!$F$2, ROW(Sheet1!$C$5:$AG$16)-MIN(ROW(Sheet1!$C$5:$AG$16))+1, Sheet1!$C$4:$AG$4), ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell A2 and  paste it down as far as needed.

Array formula in B2:

=SMALL(IF((Sheet1!$C$5:$AG$16<>Sheet1!$D$5:$AH$16)*(Sheet1!$C$5:$AG$16<>""), DATE(Sheet1!$F$2, ROW(Sheet1!$C$5:$AG$16)-MIN(ROW(Sheet1!$C$5:$AG$16))+1, Sheet1!$C$4:$AG$4), ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell B2 and  paste it down as far as needed.

Array formula in C2:

=INDEX(Sheet1!$C$5:$AG$16, SMALL(IF((Sheet1!$C$5:$AG$16<>Sheet1!$B$5:$AF$16)*(Sheet1!$C$5:$AG$16<>""), ROW(Sheet1!$C$5:$AG$16)-MIN(ROW(Sheet1!$C$5:$AG$16))+1, ""), ROW(A1)), DAY(SMALL(IF((Sheet1!$C$5:$AG$16<>Sheet1!$B$5:$AF$16)*(Sheet1!$C$5:$AG$16<>""), DATE(Sheet1!$F$2, ROW(Sheet1!$C$5:$AG$16)-MIN(ROW(Sheet1!$C$5:$AG$16))+1, Sheet1!$C$4:$AG$4), ""), ROW(A1)))) + CTRL + SHIFT + ENTER. Copy cell C2 and  paste it down as far as needed.

Download excel sample file for this tutorial.

Extract-dates-and-name-from-yearly-block-schedule.xls
(Excel 97-2003 Workbook *.xls)

Previous articles about cell blocks:

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code

Share and Enjoy:
  • Share/Bookmark

Leave a Comment

Related posts:

  1. Extract dates from a cell block schedule in excel
  2. Extract dates from a cell block schedule in excel, part 3
  3. Schedule recurring expenses in a calendar in excel (Personal Finance)
  4. Convert dates into date ranges in excel
  5. Extract dates and adjacent value in a range using a date critera in excel
  6. Convert date ranges into dates in excel
  7. Populate cells dynamically in a weekly schedule in excel
  8. Filter overlapping dates from date ranges in excel
  9. List dates outside specified date ranges in excel
  10. Schedule recurring events in a weekly schedule in excel

Leave a Reply



Extract dates from a cell block schedule in excel

Filed in Excel on Aug.29, 2010

Sam asks:

One more question for the Calendar that you have set up above can we have a excel formula which will give us a below table
StarWk EndWk Name
1 2 G
4 6 G
7 15 R ... and so on

Question found here.

Answer:

Array Formula in cell A2:

=SMALL(IF((Sheet1!$C$3:$AG$3<>Sheet1!$B$3:$AF$3)*(Sheet1!$C$3:$AG$3<>""), Sheet1!$C$2:$AG$2, ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell A2 and  paste it down as far as needed.

Array Formula in cell B2:

=SMALL(IF((Sheet1!$C$3:$AG$3<>Sheet1!$D$3:$AH$3)*(Sheet1!$C$3:$AG$3<>""), Sheet1!$C$2:$AG$2, ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell B2 and  paste it down as far as needed.

Array Formula in cell C2:

=INDEX(Sheet1!$C$3:$AG$3, SMALL(IF((Sheet1!$C$3:$AG$3<>Sheet1!$B$3:$AF$3)*(Sheet1!$C$3:$AG$3<>""), Sheet1!$C$2:$AG$2, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell C2 and  paste it down as far as needed.

Download excel sample file for this tutorial.

Extract dates and name from block schedule.xls
(Excel 97-2003 Workbook *.xls)

Previous articles about cell blocks:

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Share and Enjoy:
  • Share/Bookmark

Leave a Comment

Related posts:

  1. Extract dates from a cell block schedule in excel, part 2
  2. Extract dates from a cell block schedule in excel, part 3
  3. Convert dates into date ranges in excel
  4. Extract unique distinct numbers from closed workbook in excel (formula)
  5. Schedule recurring expenses in a calendar in excel (Personal Finance)
  6. Remove duplicates within same month or year in excel
  7. Search for a cell in a table and then display the column title in excel
  8. Schedule recurring events in a weekly schedule in excel
  9. Extract a unique distinct list by matching items that meet a criterion in excel
  10. Filter duplicates within same date, week or month in excel

Leave a Reply



Concatenate cell values in excel

Filed in Excel on Aug.23, 2010

Arielle asks:

i have a formula in row a from A1:Z1 that displays "" if there is an error. I need to then take that row and combine or concatenate all the cells into one cell and have each cell separated by " / ". I only want the " / " to display if there is text in the cell, not the "". Let me know if this is possible!

ex. A1: AA B1: BB C1: D1: ------> Z1:
(C1-Z1 display a blank ("") or [iferror(,"")])

A3: AA / BB

NOT>> A3: AA / BB / / / / / /.... /

You can find her question here:

http://www.get-digital-help.com/2009/04/08/merge-two-lists/comment-page-1/#comment-4893

Answer #1: No, vba is required. VBA Join function

Answer #2:

Formula in B1:

=A1 + Enter

Formula in B2:

=IF(A2<>"", B1&"/"&A2, B1) + Enter.

Copy formula in cell b2 and paste it down as far as needed.

Download excel file

Concatenate cells.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Share and Enjoy:
  • Share/Bookmark

Comments (4)

Related posts:

  1. Count matching cell values in two columns in excel
  2. Sum values in a range where adjacent cell value equals a criterion in excel
  3. Extract cell values in a range using a criterion in excel
  4. Extract dates from a cell block schedule in excel
  5. Search for a cell in a table and then display the column title in excel
  6. Count cell blocks in excel
  7. Extract dates from a cell block schedule in excel, part 2
  8. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2

4 Responses to “Concatenate cell values in excel”

  1. Arielle Says:
    August 23rd, 2010 at 9:41 pm

    Thanks so much!!!

  2. Rick Rothstein (MVP - Excel) Says:
    August 25th, 2010 at 2:17 am

    If anyone is interested in a VBA solution, here is a function I have posted to the newsgroups in the past. It will take the cells in a single column, or the cells in a single row, and concatenate their content placing an optional delimiter between the non-empty cell values (the default delimiter when none is specified is the empty string).

    Function JoinString(varRange As Range, Optional varDelimiter As String) As String
    With WorksheetFunction
    If varRange.Columns.Count = 1 Then
    JoinString = .Trim(Join(.Transpose(varRange.Value), varDelimiter)) 'Join down
    Else
    JoinString = .Trim(Join(.Index(varRange.Value, 1, 0), varDelimiter)) 'Join across
    End If
    JoinString = Replace(Replace(.Trim(Replace(Replace(JoinString, " ", Chr(1)), _
    varDelimiter, " ")), " ", varDelimiter), Chr(1), " ")
    End With
    End Function

  3. Rick Rothstein (MVP - Excel) Says:
    August 25th, 2010 at 2:36 am

    Actually, I posted the wrong formula. This is the one I should have posted as it is more flexible as you can specify a single range (row, column OR rectangular) or several discontiguous ranges (each of which can be a row, column or rectangular). However, this function requires you to specify the delimiter. If you want to specify no delimiter, use the empty string ("") or just omit it; however, you must include the argument delimiting comma.

    Function ConCat(Delimiter As Variant, ParamArray _
    CellRanges() As Variant) As String
    Dim Cell As Range, Area As Variant
    If IsMissing(Delimiter) Then Delimiter = ""
    For Each Area In CellRanges
    If TypeName(Area) = "Range" Then
    For Each Cell In Area
    If Len(Cell.Value) Then ConCat = _
    ConCat & Delimiter & Cell.Value
    Next
    Else
    ConCat = ConCat & Delimiter & Area
    End If
    Next
    ConCat = Mid(ConCat, Len(Delimiter) + 1)
    End Function

  4. Oscar Says:
    August 25th, 2010 at 6:08 am

    Rick Rothstein (MVP - Excel),

    Thanks for your contribution!!

Leave a Reply