extract data from one column to another

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Try adapting this.

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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top