extract data from one column to another

jammoca

Banned
In column A I have the following :

Game 1
f
d
r
y
g
t
e
w
h
j
Game 2
f
h
y
u
i
g
f
r
Game 3
d
e
f
g
y
o
p
l
k
Game 4
d
s
w
q

I would like to extract to column B everything from Game 1 down to the cell above Game 2 and into Column C everything from Game 2 down to the cell above Game 3, and into Column D everything from Game 3 down to the cell above Game 4 etc etc etc

Any ideas ?

Chris

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Peter_SSs

MrExcel MVP, Moderator
Chris

See if this is any use. Games listed across row 2. B1 formula copied across to last 'Game' column. Separate formula in next column, row 1 (F1 in the example below). B3 formula copied across and down.

Excel Workbook
ABCDEF
1Game 1112213136
2fGame 1Game 2Game 3Game 4
3dffdd
4rdhes
5yryfw
6gyugq
7tgiy
8etgo
9wefp
10hwrl
11jhk
12Game 2j
13f
14h
15y
16u
17i
18g
19f
20r
21Game 3
22d
23e
24f
25g
26y
27o
28p
29l
30k
31Game 4
32d
33s
34w
35q
36
Lists

jammoca

Banned
Once again, Peter, you've solved a problem for me AND taught me more about excel functions I hadn't found a use for before,

Thankyou,

Chris

jammoca

Banned
Let's say in Column A I have :

Game 1
1st q
s
e
w
d
f
2nd q
d
w
f
3rd q
h
r
4th q
k
f
e
w
Game 2
1st q
o
y
f
e
2nd q
p
d
e
n
3rd q
n
d
4th q
x
j
z
w
Game 3
1st q
.
.

You get the idea.

I have used and manipulated Peter SS's formula to extract the 'Game 1' data to column B and from that small list I have extracted the relevant '1st q' data to column C, the '2nd q' data to Column D the '3rd q' data to Column E and the '4th q' data to Column F.

I've also extracted the Game 2 data to Column G and had hoped to extract the relevant 1st q, 2nd q, 3rd q, 4th q data to columns H, I, J, K, but the formula is matching the first occurence of 1st q, 2nd q etc which of course, means it is spitting out the data for Game 1 1st q not Game 2 1st q etc.

Is there a way to find the position of the 2nd, 3rd, 4th, 5th, 6th etc occurence of '1st q', '2nd q', '3rd q' etc in the original list?

Peter_SSs

MrExcel MVP, Moderator

For my data, ...
B1:B2 formulas copied across to column I
Separate formula in J2
B5 formula copied across and down

Excel Workbook
ABCDEFGHIJ
1Game 1111120202020
21st q2812152126313439
3sGame 1Game 2
4e1st q2nd q3rd q4th q1st q2nd q3rd q4th q
5wsdhkopnx
6dewrfyddj
7fwfefez
82nd qdwenw
9df
10w
11f
123rd q
13h
14r
154th q
16k
17f
18e
19w
20Game 2
211st q
22o
23y
24f
25e
262nd q
27p
28d
29e
30n
313rd q
32n
33d
344th q
35x
36j
37z
38w
39
Lists 2

jammoca

Banned
Thanks, so much, Peter.

That's completely spot on.

Currently I have a userform with command buttons dedicated to sending the words .. "Game 1", Game 2" ... "Game 19", "Game 20", "1st Qtr", "2nd Qtr", "3rd Qtr", "4th Qtr", and various other stats relevant to an AFL match.

They are currently being sent to a sheet called "Raw Data" in the same workbook as the Userform, and from there various things are pulled and extracted.

In that workbook, are another 20 sheets, each one deciated to keeping permanent records of each of the 20 games, while a 21st page is deciated to displaying the results temporarily, as it is wiped at the end of each week, ready to receive the data for the next Saturday's game.

However, the processing is now extremely slow due to the size of the workbook, so I am trying to come up with coding that will send the same data, not only to the sheet "Raw Data" within the same workbook, but also to 20 other workbooks (obviously one for each game) so records can be kept permanently ( not lost when the data is wiped ready for the next game play) and speed up the processing in the 'temporary sheet', as this one is viewed while the game is in process and needs to be updating calculations quickly.

I've manipulated some coding I've picked up from searches in Mr Excel but have had limited success.

Any suggestions ?

Chris

Peter_SSs

MrExcel MVP, Moderator
Any suggestions ?
Not really, the description is a bit 'general' and in any case it sounds more like a major project than a 'quick question on MrExcel'.

Replies
4
Views
168
Replies
0
Views
149
Replies
5
Views
247
Replies
12
Views
590
Replies
6
Views
120

1,195,936
Messages
6,012,391
Members
441,695
Latest member
MickRobertson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back