Newbie Needs a Formula

[Guest]

Hello. I have a spreadsheet of data with columns as

Column A: Addres
Column B: I.D. Numbe
Column C: Inspection Dat
Column D: Due Dat
Column E: Agen

Is there a formula that would pull data based on the I.D. Number and put it in a separate worksheet? Examples of I.D. Numbers: BR-001-2004; BR-002-2004; BR-003-2004; DOD-001-2004; DOD-002-2004; CF-001-2004, and so on. I would like to pull all the "BR" numbers into one sheet; "DOD" numbers into another; "CF" into yet another, etc. Of course, all the corresponding data from the other columns would also be in the separate sheets

Can this be done

Your help and expertise is greatly appreciated

MB

 

[Jason Morin]

One option is an autofilter. For example, to show all
records where "BR" appears in the ID Number field:

1. Select all your data.
2. Go to Data > Filter > AutoFilter
3. Choose "Custom" on the drop-down list for col. B.
4. The default should be "equals". To the right insert:
*BR*
5. Copy the records to a new sheet and repeat the process.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello. I have a spreadsheet of data with columns as:

Column A: Address
Column B: I.D. Number
Column C: Inspection Date
Column D: Due Date
Column E: Agent

Is there a formula that would pull data based on the I.D.

Number and put it in a separate worksheet? Examples of
I.D. Numbers: BR-001-2004; BR-002-2004; BR-003-2004; DOD-
001-2004; DOD-002-2004; CF-001-2004, and so on. I would
like to pull all the "BR" numbers into one sheet; "DOD"
numbers into another; "CF" into yet another, etc. Of
course, all the corresponding data from the other columns
would also be in the separate sheets.

 

[Sandy Mann]

Check out Advanced Filter. Start from the sheet that you want the data in
and use BR* etc. as the criteria in the ID column.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

Hello. I have a spreadsheet of data with columns as:

Column A: Address
Column B: I.D. Number
Column C: Inspection Date
Column D: Due Date
Column E: Agent

Is there a formula that would pull data based on the I.D. Number and put

it in a separate worksheet? Examples of I.D. Numbers: BR-001-2004;
BR-002-2004; BR-003-2004; DOD-001-2004; DOD-002-2004; CF-001-2004, and so
on. I would like to pull all the "BR" numbers into one sheet; "DOD" numbers
into another; "CF" into yet another, etc. Of course, all the corresponding
data from the other columns would also be in the separate sheets.

 

[Leo Heuser]

Hello MB

Here's a formula solution.

Assuming your data in Sheet1!A2:E100
The various ID-numbers will be in sheet2, sheet3, sheet4 and sheet5.

In Sheet2:
Row 1 contains headings.

Column A: I.D. Number
Column B: Address
Column C: Inspection Date
Column D: Due Date
Column E: Agent

In A2 enter this array formula (as one line and
notice the use of mixed absolute ($$) and relative
(no cash) references):

=IF(SUMPRODUCT((LEFT(Sheet1!$B$2:$B$100,2)="br")+0)<=
ROW()-ROW($A$2),NA(), INDEX(Sheet1!$B$2:$B$100,MIN(
IF((LEFT(Sheet1!$B$2:$B$100,2)="br")*
(COUNTIF($A$1:A1,Sheet1!$B$2:$B$100)=0),
ROW(Sheet1!$B$2:$B$100)-ROW(Sheet1!$B$2)+1))))

The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later. If done correctly, Excel will display the
formula in the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself. They are Excel's way
of showing, that the formula is an array formula.

The following formulae are not array formulae, and are
entered with just <Enter>

In B2 enter

=INDEX(Sheet1!$A$2:$A$100,MATCH(A2,Sheet1!$B$2:$B$100,0))

In C2:

=VLOOKUP(A2,Sheet1!$B$2:$E$100,2,0)

In D2:

=VLOOKUP(A2,Sheet1!$B$2:$E$100,3,0)

In E2:

=VLOOKUP(A2,Sheet1!$B$2:$E$100,4,0)

Now select B2:E2 and copy down with the fill handle (the little
square in the lower right corner of the selection)

Similar for sheets3, 4 and 5.

For DOD use

LEFT(Sheet1!$B$2:$B$100,3)="dod")

for CF

LEFT(Sheet1!$B$2:$B$100,2)="cf")

etc.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hello. I have a spreadsheet of data with columns as:

Column A: Address
Column B: I.D. Number
Column C: Inspection Date
Column D: Due Date
Column E: Agent

Is there a formula that would pull data based on the I.D. Number and put

it in a separate worksheet? Examples of I.D. Numbers: BR-001-2004;
BR-002-2004; BR-003-2004; DOD-001-2004; DOD-002-2004; CF-001-2004, and so
on. I would like to pull all the "BR" numbers into one sheet; "DOD" numbers
into another; "CF" into yet another, etc. Of course, all the corresponding
data from the other columns would also be in the separate sheets.