Merging cell q

Question

Hi people,

Hoping someone can help me out here. Essentially, I am looking for a way to more speedily do what I have already done to projects 1 and 2 - migrate the scores in the 'Recorded Score' column to respective Score 1/Score 2 / Score 3 columns. The real spreadsheet is of considerable length and it will take me a long time to do this manually.

Project Name	Value Sought	Firstname	Lastname	Institution Name	Recorded Score	Score 1	Score 2	Score 3	Total Score
Project 1		First Name 1	Last Name1	University 1	5	5	5	5	5.33
Project 2		First Name 2	Last Name 2	University 2	6	6	5	2	4.33
Project 3		First Name 3	Last Name 3	University 3	4				5
Project 3		First Name 3	Last Name 3	University 3	5				5
Project 3		First Name 3	Last Name 3	University 3	6				5
Project 4		First Name 4	Last Name 4	University 4	6				4.33
Project 4		First Name 4	Last Name 4	University 4	3				4.33
Project 4		First Name 4	Last Name 4	University 4	4				4.33

Any ideas?

J

Answer 1

Hi,

How have you got the 6,5,2? Explain clearly

Answer 2

Hi Jack,

Here is another option: assume your data layout starts in A1 as shown below:

Then in G4, the first Score 1 that is blank, enter the formula:

=IF(A4<>A3,TRANSPOSE(F4:F6),"")

and copy it down as far as your data goes. Then select the range with formulas, say G4:I2000, and copy it. And then choose Paste Special, Values. At this point you have a number of different options some of which are version dependent, maybe the easiest is to select all the data range from G1 to the bottom of the data in column G and filter for all rows that are Blank and delete them.

Hope this helps

Answer 3

Try this formula in G4, Then paste result as values. After that, sort score 1 column and delete blank rows.

=IF(COUNTIF($A$2:A4,A4)=1,TRANSPOSE(FILTER($F$2:$F$9,$A$2:$A$9=A4)),"")