Thread: retrieve row of data only when a certain word is in a particular cell in that row

Hi All,

I have a query that my level of Excel knowledge is preventing me from solving. Was hoping that someone could help :-)

I have an excel file with a great deal of data from sites all around the world. What I would like to do is take data from this file but only for a specific site and have it automatically appear in a new workbook every time that site name appears.

In the example attached the supplier column (C) is the column that contains the list of sites from which i want to take the data from, but only when the cell in column C contains 'not'. if this is true, then i would like to take the whole of that row and put it into a new line in a new workbook. . . if that makes sense.

Any help would be appreciated! I have been exploring with =SUMIF. . . would that be a way of doing it?

thanks in advanceExample.xlsx

In O2 of sheet 1 add a helper column to count matches:

=IF(C2="not",COUNT(O$1:O1)+1,"")

copied down

in P2 get total count:

=MAX(O:O)

Then in Sheet2, A2:

=IF(ROWS($A$2:$A2)>Sheet1!$P$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$2:$A2),Sheet1!$O:$O)))

copied down and across as far as you need.

Column A will need to formatted as Date.