Transfer data from sheet 2 to sheet 1

Hello all, I have run out of ideas on how to do this. These forms are entered on sheet 2 in a chronological order. As they are entered, a letter from A to G is assigned to identify the area the form represents. Now, on sheet1, the data needs to be collected by Area. There are thousands of these forms. I used “B” for an example on sheet1 and there will be 6 more of these “Areas” on sheet 1 for the other 6 letters. Thanks I.A.

					Sheet 2					
	A		B		C		D		E		F			
1	Date	ID #	Data1	Data2	Data3			
2			1		x		x		x
3	Area	2				x		
4	B		3				x
5			4				x
6	Date	ID #	Data1	Data2	Data3			
7			1		x		x		x
8	Area	2				x		
9	G		3				x
10			4				x
11	Date	ID #	Data1	Data2	Data3				
12			1		x		x		x
13	Area	2				x
14	B		3				x
15			4				x

					Sheet 1							
1					B Areas							
2	Data1	Data2							Data3					
3	C2		D2		D3		D4		D5		E2
4	C12		D12		D13		D14		D15		E12

Wow. This really wants to be in a database, doesn’t it. On “Sheet 1” - each row represents a different ID#, is that correct? Should that ID appear at the beginning of the row?

lol about wow!..actually,yes and no…different ID for each row but the needed data is in the Columns on Sheet2 that are marked with an x. @mahfiaz and David…the formulas (not the numbers) =MATCH(“A”,Sheet1.A25:A33,0) and =OFFSET(Sheet1.A15,-4,3) get me close but I need auto incrementing for every 9th row and I am way over my head now lol…I probably should have put some numbers and letters instead of x’s.easier to see the whole pic I think…

@SvenGolster - if you can upload a sample spreadsheet with a bit of (anonymized, safe) data in it, that would be a help. If you don’t have enough “reputation” points for upload, can you link to Dropbox/Google Drive/wherever?

MATCH and OFFSET are your friends in a case like this. MATCH allows you to find the next place where e.g B is inside a cell. For first occurrence it’s easy. For the second one you need it to search from a smaller area, use OFFSET to exclude the previous occurrence (please note that OFFSET can return a range). Now if you have found out on which line the data starts, all you have to do is use OFFSET again to pick data from specific columns (use 1 for both width and height here).

If your data is always the same, then you don’t even need to use MATCH, just calculate the row you need, e.g A1 holds simple counter, B1=A1*9+2 (or whatever). MATCH is useful if you want to filter at the same time, eg only area B values. In that case here is an example from my worksheet, it returns the row number : A2=MATCH('GROUP-A',OFFSET(Data!$B$1,A5,0,58-A5,1),0)+A1,"") (it has 58 data rows and takes only rows which have “GROUP-A” in column B on Data sheet).

OK, am updating the database to simplify matters for me…I will link to dropbox with the final shortly asaIcan.
appreciate the concern