Look up value then determine group then copy group to different worksheet.

**kkezir** · 03-29-2013, 12:19 PM

I am not really sure where to start or if I am posting in the correct area.

I have a big data set (33000 rows, 17 columns). I want to identify 4 different surface types (PCCPAV, PCCPDJ, PCCPOL, PCCUNF) out ~100. Those surface types correspond to LRS groups. Now here is the rub. I want to copy the entire LRS group that had a PCC* surface type (the rows) into another worksheet.

If it helps.
LRS =county number and state route number
beg = begin mile post
end=end mile post
len=length
Surf=Pavement Surface type

So I am trying to pull anywhere there was PCC* surface type then I want the entire County Route and I want to copy it to Sheet 2

There will be some LRS groups that have no PCC*

I found a simular thread http://www.excelforum.com/excel-prog...copy-rows.html but I got lost since I am needing to do a 2nd function of grabbing the LRS group.

I have attached an example spreadsheet.

Thanks for any help.

**Harribone** · 03-29-2013, 12:56 PM

Seach.xlsx

Attached should do this. Sheet1 has a new column inserted at the beginning. This checks the Surf column to see if the text searched for (Sheet2 B1) exists. If it does it assigns the next lowest number to it. Then in Sheet2 there is a table lookups up these numbers to get your results listed. this works for up to 151 results on the attached. I also applied conditional formatting to this table so that it looks like the table changes size to match results.

EDIT: To allow sheet 2 to display more than 151 results highlight A155:F155 and drag down as far as required.

**kkezir** · 03-30-2013, 11:04 PM

Harribone thanks so much!!! I will chk it out when I go in to work. Hope you have good Easter.

**kkezir** · 04-01-2013, 09:36 AM

I must not have been very clear.

The LRS group 011U0005400S0 only has 2 records that as PCC* but I need the entire group copied to the 2nd sheet. There will be some LRS groups that have no PCC*, some that have a few records with PCC*, and others with all PCC*.

If any of the LRS group has any records with PCC* then I was needing to find a way to copy that entire group to Sheet 2.

Thanks for looking

**Harribone** · 04-01-2013, 11:48 AM

SearchNew.xlsx

Previous attached file just showed lines that matched the search criteria which is what I thought you wanted - I didn't fully read though you're original post sorry.
File attached on this post finds matches and then lists all items that have the same LRS code.

Let me know if there are any probs or I've missed anything (again!)

**kkezir** · 04-01-2013, 01:18 PM

Thanks soo much.. this did the trick. I learned a lot in backtracking how you did this

**Harribone** · 04-01-2013, 01:35 PM

For the benefit of others reading this:

Col B on Sheet 1 checked to see if what you want to search for exists on that row. If yes the LRS code is given as the result.
Col C on Sheet 1 then does a vlookup to see if the LRS code in Col D is anywhere in Col B. If yes then this LRS code needs to appear as a result on Sheet 2 so the result given is the next lowest number available (uses MAX formula to find biggest value in same column - above only - and then adds 1).

On Sheet 2 a vlookup is used to pull the matching data from Sheet 1 (by looking up the numbers in Col C). This is set up to find up to 150ish results (can't remember how far down I dragged this). Conditional formatting 'hides' any of these rows that don't have a result so it looks like the table always changes size to fit the number of matches.

Col A on Sheet 1 uses the same MAX method as mentioned earlier but on gives a number when Col B has a value.

Back to Sheet 2 and cell C2. This stays blank if nothing is searched for. If search text entered then it looks at Col A Sheet 1 for the largest value and returns that (i.e. the number of search matches to text). C3 does the same thing but finds the largest value in Col C Sheet 1 (i.e. the total number of results that are listed on Sheet 2).

Finally C2 also returns "None Found" when search text is entered but the highest value in Col A Sheet 1 is 0 (i.e. no matches for search).