Excel Magic Trick 1186: Extract Color From Description Field & Create Revenue by Color PivotTable

preview_player
Показать описание

See how:
1. (00:09) Problem Setup
2. (01:04) Barry Houdini at Mr Excel Message Board and pmsocho at YouTube
3. (01:23) SEARCH function with a Function Argument Array Operation to create array with position of color information
4. (02:38) LOOKUP and SEARCH function to lookup color name
5. (04:19) PivotTable to create Revenue by Color Report

Barry Houdini
Mr Excel Challenge 2007
pmsocho
Video Reference for all about LOOKUP function:
LOOKUP function Beginner to Advanced 23 Examples (Excel VLOOKUP WEEK Video #2)
  1. excelisfun
  2. Excel
  3. Microsoft Excel
  4. Learn Excel
  5. Excel How To
  6. Highline College
Рекомендации по теме
Excel Magic Trick 0:05:23

Excel Magic Trick 1186: Extract Color From Description Field & Create Revenue by Color PivotTabl...

Excel Magic Trick 0:05:23

Excel Magic Trick 1186 Extract Color From Description Field & Create Revenue by Color PivotTabl...

Excel Magic Trick 0:02:27

Excel Magic Trick 1187: Formula to Lookup Every 5th Item In a Column, INDEX and ROWS functions

Excel Magic Trick 0:05:08

Excel Magic Trick 1189: Extract Color From Description Create Revenue by Color PivotTable, 2nd Take

Excel Magic Trick 0:21:24

Excel Magic Trick 1190: Formulas To Extract Unique List County Numbers & List Zip Codes Horizont...

Excel Magic Trick 0:02:49

Excel Magic Trick 1184: Formula to Create Alpha Numeric Series X20, X25, X39…

Excel Magic Trick 0:14:14

Excel Magic Trick 1178: Conditional / AND Percentage: PivotTable Slicer, COUNTIFS or DCOUNTA?

Excel Magic Trick 0:02:25

Excel Magic Trick 1188: SUMIFS with Wildcards to Do Approximate Criteria or “Fuzzy” Criteria

Excel Magic Trick 0:05:38

Excel Magic Trick 1233: VLOOKUP to 36 Different Dynamic Tables: Table Feature & INDIRECT Functio...

Excel Magic Trick 0:04:23

Excel Magic Trick 1183: Remove All Text after Dash: Flash Fill, Text To Columns, Text Formula

Excel Magic Trick 0:06:57

Excel Magic Trick 1181: Formula to Count Cells with 4 or More Characters: COUNTIFS & SUMPRODUCT

Excel Magic Trick 0:06:59

Excel Magic Trick 1200: Conditional Format Grades To Assign Icons To 1 of 3 Groups (3 Methods)

Excel Magic Trick 0:03:33

Excel Magic Trick 1185: Calculate Standard Deviation from Frequency Distribution in Excel

Excel Magic Trick 0:04:44

Excel Magic Trick 1191: VLOOKUP: Lookup Temperature and Return to Bat Observation Table

Excel Magic Trick 0:02:40

Excel Magic Trick 1194: VLOOKUP: Data Mismatch Makes Yields #N/A, What To Do?

Excel Magic Trick 0:11:27

Excel Magic Trick 1205: Rearrange Data with INDEX Function & Formula Number Incrementers

Excel Magic Trick 0:09:53

Excel Magic Trick 1175: Decaying Average in Standards-Based Grading in Excel

Excel Magic Trick 0:04:59

Excel Magic Trick 1182: COUNTIFS and Wildcards to Count When There are 4 or More Characters

Excel Magic Trick 0:04:06

Excel Magic Trick 1202: Conditional Formatting For a Cross Tabulated PivotTable

Excel Magic Trick 0:08:22

Excel Magic Trick 1177: Flash Fill or CODE Formula Split First Last Name Based on 2nd Capital Letter

Excel Magic Trick 0:10:07

Excel Magic Trick 1201: Conditional Format Product With No Sales (5 Examples)

Excel Magic Trick 0:09:05

Excel Magic Trick 1011: Conditionally Format Row Above Matched Text Criteria 'Subtotal'

Excel Magic Trick 0:07:45

Excel Magic Trick 1195: Are YYMMDD Text Dates 1900 or 2000? Formula or Text To Columns Solution?

Excel Magic Trick 0:08:32

Excel Magic Trick 1198: Median for Each Quarter Using AGGREGATE Function

Комментарии
Автор

Incredible brains. You made it look very simple. I envy your extraordinary

Pappuloveall
Автор

Excel Magic Trick 1186: Extract Color From Description Field & Create Revenue by Color PivotTable
See how:
1. (00:09) Problem Setup
2. (01:04) Barry Houdini at Mr Excel Message Board and pmsocho at YouTube
3. (01:23) SEARCH function with a Function Argument Array Operation to create array with position of color information
4. (02:38) LOOKUP and SEARCH function to lookup color name
5. (04:19) PivotTable to create Revenue by Color Report

Barry Houdini
Mr Excel Challenge 2007
pmsocho
Video Reference for all about LOOKUP function:
LOOKUP function Beginner to Advanced 23 Examples (Excel VLOOKUP WEEK Video #2) 

excelisfun
Автор

Brilliant! Both the LookupSearch Array formula and Daniel's Isnumber/Search Sumproduct formula  Thanks for sharing!

MySpreadsheetLab
Автор

Thanks Mike.  Lookup is very powerful.  

krn
Автор

Great!! Very smart way !!
Thanks for sharing :-)

BillSzysz
Автор

bumped inadvertently into this gem.
I'd modify the formula for 2 reasons:
1. "catch" cells where no colour names appear.
2. "adapt" to 365 DAF - one formula that spills 😎😎😎😎

=BYROW(D2:D24, LAMBDA(x, IFNA(LOOKUP(2^15, FIND($G$2:$G$5, x), $G$2:$G$5), "not found")))

meniporat
Автор

Alternative approach with sumproduct:
=SUMPRODUCT(--ISNUMBER(SEARCH(G2, $D$2:$D$12083)), $B$2:$B$12083)
No need for an help column

GenePatNic
Автор

I need To make This Formula in 2007  =INFO("numfile")-25
in 2010  =INFO("numfile")-27
Why ?

=INFO("numfile")
Number of active worksheets (varies)

Reduce_Scan
Автор

For a reason unnknown to me the solution without spaces works wrong in rows 1 and 5. In 2, 3 and 4 it returns the desired result, i.e. As with spaces. I also do not understand the necessity of spaces around D2. Many thanks for the excercise!

orithelion
Автор

Thanks every1

@dritan0478 thanks for your immediate reply on twitter
@excellsFun: Please explain use of 2^15 in this formula
@danieldion: couldnot understand your formula, please help

SankalpSharmaG