Locate data and copy it to a new sheet

[anilsharaf]

This is the data which has abstract at the end of every room sitting plan:

Sitting Principle and Module.xlsm
	A	B	C	D	E	F	G	H
1	room1 (50)		Sitting Table
2		0	Colm 1	Colm 2	Colm 3	Colm 4	Colm 5	6
3		Row 10	9110	11110	9120	11120	9130
4		Row 9	9109	11109	9119	11119	9129
5		Row 8	9108	11108	9118	11118	9128
6		Row 7	9107	11107	9117	11117	9127
7		Row 6	9106	11106	9116	11116	9126
8		Row 5	9105	11105	9115	11115	9125
9		Row 4	9104	11104	9114	11114	9124
10		Row 3	9103	11103	9113	11113	9123
11		Row 2	9102	11102	9112	11112	9122
12	Entrance	Row 1	9101	11101	9111	11111	9121
13	Abstract
14	Class	From	To	Total
15	9A	9101	9130	30
16	11a	11101	11120	20
17	Total			50
18	room2 (60)
19		0	Colm 1	Colm 2	Colm 3	Colm 4	Colm 5	Colm 6
20		Row 10	9140	11210	9150	11220	9201	9211
21		Row 9	9139	11209	9149	11219	9159	9210
22		Row 8	9138	11208	9148	11218	9158	9209
23		Row 7	9137	11207	9147	11217	9157	9208
24		Row 6	9136	11206	9146	11216	9156	9207
25		Row 5	9135	11205	9145	11215	9155	9206
26		Row 4	9134	11204	9144	11214	9154	9205
27		Row 3	9133	11203	9143	11213	9153	9204
28		Row 2	9132	11202	9142	11212	9152	9203
29	Entrance	Row 1	9131	11201	9141	11211	9151	9202
30	Abstract
31	Class	From	To	Total
32	9A	9131	9159	29
33	9B	9201	9211	11
34	11b	11201	11220	20
35	Total			60
36	Room3 (25)
37		0	Colm 1	Colm 2	Colm 3	Colm 4	Colm 5
38		Row 5	9216	11225	9221	11304	9226
39		Row 4	9215	11224	9220	11303	9225
40		Row 3	9214	11223	9219	11302	9224
41		Row 2	9213	11222	9218	11301	9223
42	Entrance	Row 1	9212	11221	9217	11226	9222
43	Abstract
44	Class	From	To	Total
45	9B	9212	9226	15
46	11b	11221	11226	6
47	11c	11301	11304	4
48	Total			25
49	room4(44)
50		0	Colm 1	Colm 2	Colm 3	Colm 4	Colm 5	Colm 6
51		Row 8	X	9249	X	X	X	9234
52		Row 7	11325	9248	11318	9241	11311	9233
53		Row 6	11324	9247	11317	9240	11310	9232
54		Row 5	11323	9246	11316	9239	11309	9231
55		Row 4	11322	9245	11315	9238	11308	9230
56		Row 3	11321	9244	11314	9237	11307	9229
57		Row 2	11320	9243	11313	9236	11306	9228
58		Row 1	11319	9242	11312	9235	11305	9227
59								Entrance
60	Abstract
61	Class	From	To	Total
62	9B	9227	9249	23
63	11c	11305	11325	21
64	Total			44
TestData

I want this consolidated data in another sheet named 'consolidated'

Sitting Principle and Module.xlsm
	A	B	C	D	E
1	Classwise Abstract of All Rooms
2	Class	frm	to	total	RoomNo
3	11a	11101	11120	20	1
4	11b	11201	11220	20	2
5	11b	11221	11226	6	3
6	11c	11301	11304	4	3
7	11c	11305	11325	21	4
8	9a	9101	9130	30	1
9	9a	9131	9159	29	2
10	9b	9201	9211	11	2
11	9b	9212	9226	15	3
12	9b	9227	9249	23	4
Consolidated

Thanks in advance

 

[RobP]

Hi, well, this could get you the data into your format on Sheet2, but I can't see a way to order it in the way you have, as it doesn't seem logical.

Sub consolidate_classes()

last_row = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


For x = 1 To last_row

   
        last_row2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        row_count = last_row2 + 1
    
        Text = Replace(UCase(Range("A" & x)), " ", "")
        Room = Mid(Text, 5, InStr(5, Text, "(") - 5)
        
        x = x + 1
    
        
        Do While Range("A" & x) = "" Or UCase(Range("A" & x)) = "ENTRANCE" Or UCase(Range("A" & x)) = "ABSTRACT"
        
            x = x + 1
                
        Loop
    
    
            For y = x + 1 To last_row
            
                next_text = UCase(Range("A" & y))
     
                                
                    If next_text <> "TOTAL" Then
                        Sheets("Sheet2").Range("E" & row_count) = Room 'store room number on Sheet2
                        Range("A" & y, "D" & y).Copy Sheets("Sheet2").Range("A" & row_count, "D" & row_count)
                        row_count = row_count + 1
                        x = x + 1
                    Else
                        x = x + 1
                        Exit For
                    End If
             Next y
                                                    
Next x

End Sub

 

[anilsharaf]

Hi, well, this could get you the data into your format on Sheet2, but I can't see a way to order it in the way you have, as it doesn't seem logical.

Sub consolidate_classes()

last_row = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


For x = 1 To last_row

  
        last_row2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        row_count = last_row2 + 1
   
        Text = Replace(UCase(Range("A" & x)), " ", "")
        Room = Mid(Text, 5, InStr(5, Text, "(") - 5)
       
        x = x + 1
   
       
        Do While Range("A" & x) = "" Or UCase(Range("A" & x)) = "ENTRANCE" Or UCase(Range("A" & x)) = "ABSTRACT"
       
            x = x + 1
               
        Loop
   
   
            For y = x + 1 To last_row
           
                next_text = UCase(Range("A" & y))
    
                               
                    If next_text <> "TOTAL" Then
                        Sheets("Sheet2").Range("E" & row_count) = Room 'store room number on Sheet2
                        Range("A" & y, "D" & y).Copy Sheets("Sheet2").Range("A" & row_count, "D" & row_count)
                        row_count = row_count + 1
                        x = x + 1
                    Else
                        x = x + 1
                        Exit For
                    End If
             Next y
                                                   
Next x

End Sub

Thank You very much It worked fine. I have to only sort it to order it in the way I have.