Hi
I wonder if anyone can help me to extract certain figures using a formula that only picks up a number in a column if the word at the side matches the request? Although the word in column E is always the first word on the row, there are usually different numbers and letters after the word in that cell.
Column E | Column F |
---|---|
Client | 100 |
Prospect | 20 |
Older | 30 |
Prospect | 50 |
Older | 60 |
Client | 80 |
To either give just the totals under headings for Client, Prospect and Older OR list the items under Client, Prospect and Older headings as:
Client | Prospect | Older |
100 | 20 | 30 |
80 | 50 | 60 |
OR if it is easier, just the totals as:
Client | Prospect | Older |
180 | 70 | 90 |
Many Thanks |
Replies (10)
Please login or register to join the discussion.
Sort it and use subtotals ... or
If there are a limited number of possible codes then use if() to put values into appropriate columns.
You might be best emailing me an example I would do the changes and then post here an explanation [email protected]
One solution is to use text to columns (on the Data tab) then delete all but the first of the new columns. You will then have just the first word. Turn it into a Table (Ctrl + T) and then filter or use a Pivot table. Or you could use If and filter out blanks.
The problem with this is that Text to Columns is not dynamic so will need to be run each time the underlying data changes.
So a better solution:
Turn your data into a table
Add a new column and type into the first cell of the new column =LEFT(A1,FIND(" ",A1)) where A1 is for instance Prospect 123 (Assumption: There is always a space after the first word). The rest of the column should self populate.
You can now filter for your target word or use a pivot table
Use something like =IF(E1="Client",D1,0)
I'd put this formula into an analysis column, eg G1, for Clients and use columns H, I etc for headings of Prospect, Older, Client etc
If the word Prospect, Older, Client contain text before or after then use something like =ISNUMBER and the =SEARCH function in Excel to find cells containing specific text. See https://www.excel-easy.com/examples/contains-specific-text.html for the syntax.