Help with a formula

[Guest]

Is there a formula that I could use to perform the following.

In one spreadsheet I have a unique no e.g.1018. In another spreadsheet I
have lots of rows some of which have the unique no 1018. I need a formula to
say that if any rows in the second spreadsheet equal 1018 then return all
those rows.

Thanks

 

[Bernard Liengme]

I typed your number (1018) into some cells in columns D and J of Book3
In Book2 in A1 I entered your number (1018)
In B1 I entered =COUNTIF([Book3]Sheet1!$D$1:$D$18,A1) to count the number of
occurrences in column D of the other file
Or I could use =COUNTIF([Book3]Sheet1!$D$1:$J$18,A1) to count the number of
occurrences in a block
If the sheet name has spaces in it you need something like
=COUNTIF('[Book3]Nov Data'!$D$1:$D$18,A1) Note the single quotes

It is best enter the formula in this manner:
type =COUNTIF(
open the second file and with the mouse select the range of cells to get
'[Book3]Nov Data'!$D$1:$D$18
return to first book
complete formula with: ,A1)
best wishes

 

[Guest]

I think I may not have explained this enough.

I don't want to count the number of rows that contain 1018. I want it to
list the rows individually e.g.
row 5 1018 Joe Blogs
Row 22 1018 Sam Jones
Row 40 1018 Mary Smith

 

[Bernard Liengme]

Might be able to use VLOOKUP or may have to use MATCH with INDEX depending
how the data is set out. Would it be possible for you to tell us how the
data is set out?
best wishes

 

[Guest]

Bernard

This is an example of all the data that I want returned.
1018 P/27275 305249 TRIBAL EDUCATION
LTD 01/01/07 Open 10/25/07 PA2171 PORD 10/01/00 16500
1018 P/27276 305249 TRIBAL EDUCATION
LTD 01/01/07 Open 10/25/07 PA2171 PORD 10/01/00 4935
2020 C/10449 303001 SHUTTLE BUSES LTD 01/01/07 Part
Matched 09/13/07 PA0568 CORD 09/01/00 23500
2020 K/11324 303001 EAGLE COACHES 01/01/07 Part
Matched 09/18/07 PA1731 KORD 09/01/00 3960
2021 K/11331 301015 PC
WORLD 01/01/07 Posted 09/27/07 PA3910 KORD 09/01/00 629.3


I need all columns returned.

 

[Roger Govier]

Hi

Could you not just apply Autofilter to your set of data, and use the
dropdown on column A to select the value you want?

If you are wanting to extract those rows to another sheet, then use Advanced
Filter, remembering to begin the filter from the destination sheet.
For more help on this take a look at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs