Multiple results from one criteria?

  • Thread starter Thread starter Tat
  • Start date Start date
T

Tat

What formula would you use for an array to get multiple
results from one criteria?

Example:
A B
name Smith,Bill
address 999 Somewhere
name Jones, Pat
address 123 Here

How do I go about getting a list of names using only one
formula?
 
In addition to this question, I am exporting info form
EasyPay but it imports in a messy way. I need to use a
formula to extract specific ingo because not all the
information for each employee is located in the same
column.
 
My suggestion would be an advanced filter that returns all
rows where cells in column B do *not* contain a number:

1. Select range and go to Data > Filter > Advanced Filter
2. In the criteria range, enter the range (for example),
E6:E7, where E6 is empty and E7 contains the formula:

=SUMPRODUCT(1*ISERROR(1*MID(B2,ROW(INDIRECT("1:"&LEN
(B2))),1)))=LEN(B2)

Put the formula in E7 first, then do step 1.

HTH
Jason
Atlanta, GA
 
I tried this but it did not work. Here is an actual
exported file that I am dealing with:

Code : Name : Smith, Jacque line
Type/F

LIFE 7.78 RRSP RgBen 385.31 WSIB 124.19
RRSP% 192.65 LTD RgDed 104.94 LIFE 5.18
AD&D 2.16 EHC RgDed 27.22 DENTAL 29.28
VST-AD 10870.65 VST-PE SpPay 6942.6
OBP 995.21
STATEI 795.3 OTHER SpPay 300
------- -------- ----------- -------- ---
------ ------- --------
Code : Name : Jones, Septemb er Type/F

LIFE 7.78 RRSP RgBen 373.12 WSIB 109.34
RRSP% 186.56 LTD RgDed 83.94 LIFE 5.18
AD&D 2.16 EHC RgDed 8.68 DENTAL 12.62
VST-AD 302.88 OBP 302.89
STATEI 623.42 OTHER SpPay 240
------- -------- ----------- -------- ---
------ ------- --------

Ideally, I would like to extract the information that I
need from this export from EasyPay. Notice the employee
name ends up broken in 3 columns.

I would like the following info extracted from each
employee.

Enployee Name Vst-AD Vst-PE OBP
Smith, Jacqueline 10870.65 6942.60 995.21
Jones, September 302.88 302.89

You will notice that the same information for the above
two employees is not located in the same columns.
 
I tried this but it did not work. Here is an actual
exported file that I am dealing with:

Code : Name : Smith, Jacque line
Type/F
Click to expand...

Line wrapping no big deal, but are there really breaks in names, i.e., would
'Jacque' and 'line' really be separated by multiple spaces or appear in
different cells?
LIFE 7.78 RRSP RgBen 385.31 WSIB 124.19
RRSP% 192.65 LTD RgDed 104.94 LIFE 5.18
AD&D 2.16 EHC RgDed 27.22 DENTAL 29.28
VST-AD 10870.65 VST-PE SpPay 6942.6
OBP 995.21
STATEI 795.3 OTHER SpPay 300
------- -------- ----------- -------- ---
------ ------- --------
Code : Name : Jones, Septemb er Type/F

LIFE 7.78 RRSP RgBen 373.12 WSIB 109.34
RRSP% 186.56 LTD RgDed 83.94 LIFE 5.18
AD&D 2.16 EHC RgDed 8.68 DENTAL 12.62
VST-AD 302.88 OBP 302.89
STATEI 623.42 OTHER SpPay 240
------- -------- ----------- -------- ---
------ ------- --------

Ideally, I would like to extract the information that I
need from this export from EasyPay. Notice the employee
name ends up broken in 3 columns.

I would like the following info extracted from each
employee.

Enployee Name Vst-AD Vst-PE OBP
Smith, Jacqueline 10870.65 6942.60 995.21
Jones, September 302.88 302.89

You will notice that the same information for the above
two employees is not located in the same columns.
Click to expand...

This is most definitely *NOT* something spreadsheets do well. There's a
commercial product named Monarch that could do this.

http://monarch.datawatch.com/

Also, most scripting languages (Perl, Python, Ruby, awk) could do this with less
effort than any spreadsheet. Are you absolutely limited to using only Excel to
do this?
 
I arranged the columns to be exported from EasyPay to
separate the amounts from the item ie VST-AD. In this way
I could add up the columns of figures. Unfortunately the
names are then cut up into columns as well.

Right now, all I have to work with is Excel. I've never
heard of Monarch but will look into it and the other
scripting languages you wrote about. Thanks for the info.
In the meantime is there anything else I could do?
 
I arranged the columns to be exported from EasyPay to
separate the amounts from the item ie VST-AD. In this way
I could add up the columns of figures. Unfortunately the
names are then cut up into columns as well.

Right now, all I have to work with is Excel. I've never
heard of Monarch but will look into it and the other
scripting languages you wrote about. Thanks for the info.
In the meantime is there anything else I could do?
Click to expand...

Not easily. If you can change around the columns in EasyPay, is there an option
for displaying one item per row? For example,

Code :
Name : Smith, Jacqueline Type/F
LIFE 7.78
RRSP RgBen 385.31
WSIB 124.19
RRSP% 192.65
LTD RgDed 104.94
LIFE 5.18
AD&D 2.16
EHC RgDed 27.22
DENTAL 29.28
VST-AD 10870.65
VST-PE SpPay 6942.6
OBP 995.21
STATEI 795.3
OTHER SpPay 300

? If not, is there any way to eliminate the RgBen, RgDed and SpPay entries, or
move them to the last column? Or does EasyPay have any other export formats? The
dog's lunch you've shown can be parsed in Excel, but it's difficult and fragile
(meaning it's almost impossible to ensure it'd work for every record).
 
Unfortunately, EasyPay is not that flexible with their
reports. It is set only one way, however when I export it
into Excel I can get rid of the RGBen,SpPay column so that
what I'm left with is the item and the amount. (The reason
I left that in was because without it the first name of
the employee would not be exported...no big deal.) There
is no way that I can export the info into one column of
info for each employee. So now it looks like this:

Code : Name : Smith, ne Type/F

LIFE 7.78 RRSP 385.31 WSIB 124.19
RRSP% 192.65 LTD 104.94 LIFE 5.18
AD&D 2.16 EHC 27.22 DENTAL 29.28
VST-AD 10870.65VST-PE 6942.6 OBP 995.21
STATEI 795.3 OTHER 300
------- -------- ----------- ---------
Code : Name : Smith, er Type/F

LIFE 7.78 RRSP 373.12 WSIB 109.34
RRSP% 186.56 LTD 83.94 LIFE 5.18
AD&D 2.16 EHC 8.68 DENTAL 12.62
VST-AD 302.88 OBP 302.89
STATEI 623.42 OTHER 240

It is in 6 distinct columns. What next?
 
Back
Top