Combine data in 2 rows into 1 single row

[idealist87]

Hi experts

Hopefully an easy fix for you.

I have cricket data. Each game data is available is 2 rows (1 row per team). I want both the teams data in 1 row, as per the Output tab.

If you have any questions, please message

Thank you

[thenewkidd]

I have several questions.
Is this a pedigree chart of sorts?
meaning row 2 and row 3 played each other
row 4 and row 5 played each other
row 6 and row 7.... etc etc?
and it will never be row2 playing row 6 for example. Odd Rows always play Even Rows (Odd plays the even below them and Even always plays the Odd above them)
Pictured is the grouping I'm talking about.

also, Merged Cells are difficult to work with, I recommend breaking away from that if possible.

[thomglea]

On Output sheet, cell A3, enter this and copy down.

Please Login or Register  to view this content.

On Output sheet, cell B3, enter this and copy to the right and down:

Please Login or Register  to view this content.

[idealist87]

yes, row 2 and 3 played against each other and so on....

yes, you are correct --> (Odd plays the even below them and Even always plays the Odd above them) - you can use the date as a guide as a well - only 1 match played per day

Please go ahead with your idea of breaking the merged cell

[idealist87]

Hi

I got a similar problem with 4 rows need to be combined a 1 row

Could you please look into this?

I have also posted this as a separate thread.

Thanks

[HansDouwe]

2-row merge

Everything in 1 go, please try:

Please Login or Register  to view this content.

[HansDouwe]

4-row merge

Everything in 1 go, please try:

Please Login or Register  to view this content.

[HansDouwe]

Glad to have helped, thanks for the rep .

[thenewkidd]

yes, row 2 and 3 played against each other and so on....

yes, you are correct --> (Odd plays the even below them and Even always plays the Odd above them) - you can use the date as a guide as a well - only 1 match played per day

Please go ahead with your idea of breaking the merged cell

Instead of a merged cell, "Center Across Section" the two cells.
Center Across Section only works for a single row and doesn't work for columns

Highlight the cells that you would merge, though instead of clicking "merge cells" right click and choose "format Cells..."

Click the Alignment Tab and in the "horizontal" drop down select "Center Across Section"

Then click OK.

This is best practice as sometimes merged cells can mess with formulas, it can be done, but this way you don't have to worry about it.

Hope this helps.

[idealist87]

4-row merge

Everything in 1 go, please try:

Please Login or Register  to view this content.

HI

The total data I got is 7000 rows. The formula is not showing all the data. It stops with 5 rows as per example data.
How to fix this?

[HansDouwe]

I suspect that after row 20 or so there are no more values in column A.

You have 3 options:

Put values in column A

OR try ...B2:P9999... and decrease these numbers by 1: {2,9,9,10,10,14,14,12,12,13,13,12,12,13,13}

OR replacee INDEX(t,,1) by INDEX(t,,2)

[sandy666]

Please check attached files to see if this is what you want to achieve

[idealist87]

Please check attached files to see if this is what you want to achieve

Yes,it's correct

But when I add all my data , 7000 rows into Data tab, how should I update the output tab?

[idealist87]

I suspect that after row 20 or so there are no more values in column A.

You have 3 options:

Put values in column A

OR try ...B2:P9999... and decrease these numbers by 1: {2,9,9,10,10,14,14,12,12,13,13,12,12,13,13}

OR replacee INDEX(t,,1) by INDEX(t,,2)

No luck mate.

[sandy666]

Yes,it's correct

But when I add all my data , 7000 rows into Data tab, how should I update the output tab?

just refresh green table (right click on this table and choose Refresh or Alt+F5)

[HansDouwe]

The array-formula was not correct confirmed with CTRL+SHIFT+ENTER

Please delete all green cells and add the formula again to A3 and confirm the formula with CTRL+SHIFT+ENTER

[sandy666]

my pleasure and thanks for the rep
have a nice day