The cell range is A1:A10 and contain words.

The array formula in C2 contains an udf:

SplitWords($A$1:$A$10) + CTRL + SHIFT + ENTER. Copy cell C2 and paste it down as far as needed.

User defined function:

Option Explicit

Function SplitWords(rng As Range) As Variant()

Dim x As Variant

Dim Wrds() As Variant

Dim Cells_row As Long

Dim Cells_col As Long

Dim Words As Long

Dim y() As Variant

ReDim y(0)

Wrds = rng.Value

 For Cells_row = LBound(Wrds, 1) To UBound(Wrds, 1)

  For Cells_col = LBound(Wrds, 2) To UBound(Wrds, 2)

   x = Split(Wrds(Cells_row, Cells_col))

   For Words = LBound(x) To UBound(x)

    y(UBound(y)) = x(Words)

    ReDim Preserve y(UBound(y) + 1)

   Next Words

  Next Cells_col

 Next Cells_row

SplitWords = Application.Transpose(y)

End Function

How to use this user defined custom excel function

1. Click "Developer" tab on the ribbon
   How to enable developer tab on the ribbon
2. Click "Visual Basic" button
3. Insert a new module
4. Copy this udf example and paste it into new module
5. Select a sheet
6. Use udf in a cell, example SplitWords($A$1:$A$10) + CTRL + SHIFT + ENTER

Download excel file

extract words from a range udf.xls
(Excel 97-2003  Workbook *.xls)

Related posts:

1. Calculate your stock portfolio performance 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. Split data across multiple sheets in excel (vba)
5. Stock alerts in excel
6. Create a monthly date range in excel
7. Create a quartely date range in excel
8. Concatenate cell values in excel

I have down loaded the consolidated file and it does not appear to work the way I expected.

I am looking to combine cashflow worksheets for multiple projects. The row headings are the same for each project but the column heading varies (they are set as end of month dates)

The issue with normal consolidation is that each sheet has to be identical. But because each project starts and stops at different months the consolidation is messy

Answer:

I am not sure I completely understand but I gave it a try.

After clicking the "Consolidate Sheets" button:

Download excel file

Consolidate-sheets-row headers the same.xls

(Excel 97-2003  Workbook *.xls)

Remember to backup your original excel file. You can´t undo a macro

I do realize this code is not the shortest or the most efficient.

VBA code:

Option Explicit

Sub Consolidate()

Application.ScreenUpdating = False

Dim csShts        As Range

Dim clmnheader        As Range

Dim sht As Worksheet

Dim LastCol As Integer

Dim i As Long

Set csShts = Worksheets("Consolidate").Range("A2")

Set clmnheader = Worksheets("Consolidate").Range("B1")

Do While csShts <> ""

    For Each sht In Worksheets

    i = 0

    If sht.Name = csShts Then

        sht.Select

        Range("A1").Select

        Do While Selection <> ""

            Set clmnheader = Worksheets("Consolidate").Range("B1")

            Do While clmnheader <> ""

                If clmnheader.Value = Selection.Value Then

                    'Find last column in row

                    With ActiveSheet

                        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

                    End With

                End If

                    If LastCol > i Then

                        i = LastCol

                    End If

                Set clmnheader = clmnheader.Offset(1, 0)

            Loop

        ActiveCell.Offset(1, 0).Select

        Loop

        sht.Select

        Range("A1").Select

        Set clmnheader = Worksheets("Consolidate").Range("B1")

        Do While Selection <> ""

            Set clmnheader = Worksheets("Consolidate").Range("B1")

            Do While clmnheader <> ""

                If clmnheader.Value = Selection.Value Then

                    Set clmnheader = clmnheader.Offset(0, 1)

                    'Copy range

                    Do While Selection.Column <= i

                        ActiveCell.Offset(0, 1).Select

                        Selection.Copy

                        clmnheader.Insert Shift:=xlToRight

                    Loop

                    ActiveCell.Offset(0, -i).Select

                    Set clmnheader = clmnheader.Offset(0, -i)

                End If

                Set clmnheader = clmnheader.Offset(1, 0)

            Loop

        ActiveCell.Offset(1, 0).Select

        Loop

    End If

    Next sht

Set csShts = csShts.Offset(1, 0)

Loop

Sheets("Consolidate").Select

End Sub

Related posts:

1. Consolidate sheets in excel (vba)
2. Split data across multiple sheets in excel (vba)
3. Categorize values into multiple columns using vba in excel
4. Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
5. Combine data from multiple sheets in excel
6. Scan stock markets in excel
7. Count unique distinct numbers across multiple sheets (3D range) in excel
8. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
9. Create a dynamic stock chart using a web query and a drop down list in excel

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?

How can I count the number of transactions per person?

Answer:

First create a named dynamic range. If you add new values to your list the named range expands. This makes it easy to update your pivot table with newly added values. Then create a pivot table.

How to create a dynamic named range

This dynamic range formula adds both new columns and rows.

1. Click "Formula" tab on the ribbon
2. Click "Name Manager"
3. Click "New"
4. Type a name for the range, I named it "table"
5. Type this formula into "Refers to:" field:=OFFSET(Table!$A$1, 0, 0, COUNTA(Table!$A:$A), COUNTA(Table!$1:$1))
   
   
6. Click OK

The above table has the cell range $A$1:$C$11.

The dynamic range formula =OFFSET(Table!$A$1, 0, 0, COUNTA(Table!$A:$A), COUNTA(Table!$1:$1)) returns the same cell reference $A$1:$C$11.

How to create a pivot table in excel 2007

1. Select cell range A1:C11
2. Click "Insert" tab on the ribbon
3. Click "Pivot table" button

   

4. Type "table" in "Table/Range:" field
5. Select where you want the pivot table to be placed
6. Click OK

I placed the pivot table on a new sheet.

1. Drag and drop "Name" to "Drop Row Fields Here"
2. Drag and drop "Branch" to "Drop Column Fields Here"
3. Drag and drop "Amount" to "Drop Column Fields Here"

How to create a list of unique distinct values for branch A

1. Click Branch arrow
   
   
2. Deselect all but Branch A
3. Click OK!

How to count the number of transactions per person

Drag and drop "Name" into "Values". See picture below.

How to update pivot table with new values

1. Add more values to table
2. Right click Pivot table
3. Click "Refresh"

Download excel sample file for this tutorial.

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

Functions in this article:

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

COUNTA(value1,[value2],)
Counts the number of cells in a range that are not empty

Related posts:

1. Extract a unique distinct list by matching items that meet a criterion in excel
2. Create dependent drop down lists containing unique distinct values in excel
3. Dynamic stock chart in excel – Add date ranges
4. Extract all rows from a range that meet criteria in one column in excel
5. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
6. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
7. Create a dynamic stock chart using a web query and a drop down list in excel
8. How to return the total for orders with more than one item in excel
9. Extract cell values in a range using a criterion in excel
10. Search for a cell in a table and then display the column title in excel

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

Related posts:

1. Extract a unique distinct list by matching items that meet a criterion in excel 2007 using pivot table
2. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
3. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
4. Validate unique distinct list in excel
5. Unique distinct list sorted based on occurrance in a column in excel
6. Create a unique distinct sorted list containing both numbers text removing blanks in excel
7. Extract unique distinct numbers from closed workbook in excel (formula)
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. Create a unique distinct text list from a range containing both numerical and text values in excel

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

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

(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:

• 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

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. User defined function to split words in a cell range into a cell each in excel

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:

• 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

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. User defined function to split words in a cell range into a cell each in excel
4. Convert dates into date ranges in excel
5. Schedule recurring expenses in a calendar in excel (Personal Finance)
6. Extract unique distinct numbers from closed workbook in excel (formula)
7. Remove duplicates within same month or year in excel
8. Search for a cell in a table and then display the column title in excel
9. Schedule recurring events in a weekly schedule in excel
10. Filter duplicates within same date, week or month in excel

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

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. User defined function to split words in a cell range into a cell each in excel
4. Extract cell values in a range using a criterion in excel
5. Extract dates from a cell block schedule in excel
6. Search for a cell in a table and then display the column title in excel
7. Count cell blocks in excel
8. Extract dates from a cell block schedule in excel, part 2

Any idea how to adapt the formula to do a conditional (i.e. comma-separated) merge of the data from multiple columns, in the case that a single row has emails spread over several columns?

You can find magneticone´s question here: How to extract email addresses from a excel sheet

Answer:

Array formula in E2:

=INDEX($A$1:$C$3, SMALL(IF(ISNUMBER(SEARCH("@", $A$1:$C$3)), ROW($A$1:$C$3)-MIN(ROW($A$1:$C$3))+1, ""), ROW(A1)), (SMALL(IF(ISNUMBER(SEARCH("@", $A$1:$C$3)), (ROW($A$1:$C$3)-MIN(ROW($A$1:$C$3))+1)+COLUMN($A$1:$C$3)/1048576, ""), ROW(A1))-SMALL(IF(ISNUMBER(SEARCH("@", $A$1:$C$3)), ROW($A$1:$C$3)-MIN(ROW($A$1:$C$3))+1), ROW(A1)))*1048576) + CTRL + SHIFT + ENTER. Copycell E2 and paste it down as far as needed.

Every cell containing "@" is filtered into column E.

Concatenate cells in column E

Unfortunately you need some vba code to pull this one off: VBA Join function

Download excel template

Filter emails spread over several columns.xls
(Excel 97- 2003 Workbook *.xls)

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

COLUMN(reference) returns the column number of a reference

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

Related posts:

1. Filter duplicate values in a range using “contain” condition in excel
2. Filter unique text values in a range using “contain” condition in excel
3. Filter unique distinct text values in a range using “contain” condition in excel
4. Vlookup of three columns to pull a single record
5. Filter unique distinct values where adjacent cells contain search string in excel
6. Lookup with multiple criteria and display multiple search results using excel formula, part 4
7. Filter unique distinct values using “contain” condition of a column in excel
8. Lookup two index columns returning multiple matches in excel
9. Lookup two index columns using min max values and a date range as criteria
10. Lookup two index columns in excel

search value in cell e1: AA

table in cells A1:C6

A1:x B1:y C1:z
A2:BB B2:CC C2:DD
A3:AA B3:GG C3:AA
A4:CC B4:BLANK C4:EE
A5:FF B5:BLANK C5:HH
A6:BLANK B6:BLANK C6:II

then the values to be displayed from the search would be: x in one cell and z in the next cell.

the display of the search values can either be in a row-(g1: x h1: z) or a column-(g1: x g2: z) Let me know if this type of search is possible, thanks!

Answer:

Array formula in E3:

=INDEX($A$1:$C$1, SMALL(IF(COUNTIF($E$1, $A$2:$C$6)>0, COLUMN($A$2:$C$6)), ROW(A1))) + CTRL + SHIFT+ ENTER. Copy cell E3 and paste it down as far as needed.

Download excel template

Search for a cell in a table and then display the column title.xls
(Excel 97- 2003 Workbook *.xls)

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

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

COLUMN(reference) returns the column number of a reference

Related posts:

1. Lookup with multiple criteria and display multiple search results using excel formula, part 4
2. Search for multiple text strings in multiple cells and use in data validation in excel
3. Sum adjacent values using multiple lookup text values in a column in excel
4. Concatenate cell values in excel
5. Filter unique distinct values where adjacent cells contain search string in excel
6. Filter a column and create a new unique list sorted from A to Z using array formula in excel
7. Extract cell values in a range using a criterion in excel
8. Extract all rows from a range that meet criteria in one column in excel
9. Extract a unique distinct list by matching items that meet a criterion in excel 2007 using pivot table