Transfer data from sheet 2 to sheet 1

[SvenGolster]

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
5

[dajare]

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?

[SvenGolster]

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…

[dajare]

@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?

[mahfiaz]

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).

[mahfiaz]

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).

[SvenGolster]

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