Link to home
Create AccountLog in
Avatar of KatZoo
KatZooFlag for United States of America

asked on

Find list of consecutive numbers in an excel column

I'm new to VBA and need your help.  I am working with two worksheets.  The first worksheet has a list of consecutive numbers from 1 to 40,000 in column A.  Columns B through AA have data associated with the numbers in column A.   The numbers in col. A identify samples we test in a lab.  After a day's worth of samples has been tested, a blank row is inserted into this worksheet so that in looking for these consecutive numbers, a blank cell may be encountered.  From the second worksheet (which becomes a report for a client),  I have the beginning ($Q$2) and ending ($S$2) lab numbers that represent the samples submitted by a single client.    To prepare the report for that client, I need to find that list of consecutive lab numbers (represented by the beginning and ending lab numbers) in col A of the 1st worksheet and copy into the 2nd worksheet associated data from some of the other columns in the 1st worksheet (for example:  the data in worksheet 1, col C must be copied into worksheet2 col B).  I have no code because nothing I have tried or researched has worked.  This is excel 2007 with an XP Professional 2002 operating system.
Your assistance is appreciated.
Avatar of Jerome Hayes
Jerome Hayes
Flag of United States of America image

Hello Kat,

If possible, could you attached a sample file?

Avatar of Rob Henson
Rather than copy the data can it just be referenced using a VLOOKUP

On worksheet 2, in Q3 put:

=VLOOKUP(Q$2,Sheet1!$A:$A,$P3,false)      Notice the $ signs.

In column P put the column offset from worksheet 1 for the data that needs to be retrieved, col A being column 1, B = 2 etc.

Then copy and paste across columns R & S

This will return the value from the column offset by the value in P for the row in which it finds the value in Q2.

Cheers
Rob H
Should have added to copy down as required down columns Q to S once pasted across.

Also the column reference that I suggested go in column P could be anywhere, off to the right outside of the report print area so long as the offset part of the formula refers to the right cell.

Fo rexample if you only needed the 2nd 3rd 5th 14th and 21st column of data from the list on sheet 1, populate column P3 to P7 with 2, 3, 5, 14 & 21 and copy the vlookup formula down the 5 rows.

The $ signs in the formula make sure that when copied the formula always looks for the value in row 2 in column A on sheet 1 and offsets the value found in column P.

The false deals with the need to find an exact match rather than a close match.

Cheers
Rob H
Avatar of KatZoo

ASKER

file of both worksheets attached
Experts-Copy.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of KatZoo

ASKER

Still working on following these formulas, but everything I needed was in place.  I still need a few columns copied over to the second sheet, but the formulas given can be used to get those.  Thanks much.
Apologies, I should have commented in my post that I had only added formulas for those that were obvious and further work would be needed to get the remaining cells populated.

Cheers
Rob H