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.
Your assistance is appreciated.
Programming Languages-OtherMicrosoft ExcelVisual Basic Classic
Last Comment
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,
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
On worksheet 2, in Q3 put:
=VLOOKUP(Q$2,Sheet1!$A:$A,
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
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
ASKER
file of both worksheets attached
Experts-Copy.xlsx
Experts-Copy.xlsx
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
Cheers
Rob H
If possible, could you attached a sample file?