Transferring and Transposing Data

**tomvh444** · 09-16-2011, 07:46 PM

I have attached 2 sample worksheets. I am wanting to fill and also transpose data from worksheet 1 to worksheet 2. On worksheet 1 in columns I and J either an L or a W appears based on data that I enter in Columns F, G, and H.

This data I enter in columns F, G, and H also appears in columns E, F, and G on worksheet 2 in two different place. In one it appears the same and in the other it is transposed to reflect what those numbers are for the opposing team.

What I am looking to do is also have the L or W that appears in columns I and J on worksheet 1 also appear in columns H and I on worksheet 2 in the appropriate cells (being the same for the team listed in column C on worksheet 1 and transposed from L to W or W to L for that opponent listed in column E on worksheet 1).

I have manually entered the L's and W's as they should appear on worksheet 2.

Hopefully I have clearly explained the situation.

**beaunydal** · 09-17-2011, 07:38 AM

This array formula seems to work. It's a bit long though...

Please Login or Register  to view this content.

Beau Nydal

**tomvh444** · 09-17-2011, 12:23 PM

Wow, you are right it is definitely the longest array I have ever seen, but it works perfectly. many Thanks Beau, Best Regards, Tom

**beaunydal** · 09-17-2011, 05:27 PM

One thing I should have warned you about is that it is a multi-valued array formula, it returns two values, ATS in the H cell and SU in the I cell.
So, before you start dragging the fill-handle to fill it down to get it into further rows, make sure you select an H cell and an I cell.
If you only select an H cell then drag the fill handle then repeat the process after selecting an I cell, the ATS value will be correct but the SU value will simply be a repeat of the ATS value. The two columns must be filled down together.

Beau Nydal

**beaunydal** · 09-17-2011, 06:14 PM

If you prefer instead to avoid this potential hazard and use single-valued array formulae then the current multi-valued formula is easily converted to single-valued.
The current formula has two zeroes that can either be changed to 1 for the H4 formula and 2 for the I4 formula or to COLUMN(H4)-COLUMN($H4)+1 for the H4 formula so that it can be filled across to column I.

Current multi-valued H4:I4 formula with the two zeroes bold and red...

IF($A4="","",IF(ISNUMBER(MATCH($A4,INDEX('1-Weekly Games'!$C:$C,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$C:$C,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0)),INDEX(INDEX('1-Weekly Games'!$I:$J,MATCH($B4,'1-Weekly Games'!$B:$B,0),1):INDEX('1-Weekly Games'!$I:$J,MATCH($B4+1,'1-Weekly Games'!$B:$B),2),MATCH($A4,INDEX('1-Weekly Games'!$C:$C,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$C:$C,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0),0),CHOOSE(--(INDEX(INDEX('1-Weekly Games'!$I:$J,MATCH($B4,'1-Weekly Games'!$B:$B,0),1):INDEX('1-Weekly Games'!$I:$J,MATCH($B4+1,'1-Weekly Games'!$B:$B),2),MATCH($A4,INDEX('1-Weekly Games'!$E:$E,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$E:$E,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0),0)="W")+1,"W","L")))

Single-valued H4 formula (zeroes changed to 1)...

IF($A4="","",IF(ISNUMBER(MATCH($A4,INDEX('1-Weekly Games'!$C:$C,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$C:$C,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0)),INDEX(INDEX('1-Weekly Games'!$I:$J,MATCH($B4,'1-Weekly Games'!$B:$B,0),1):INDEX('1-Weekly Games'!$I:$J,MATCH($B4+1,'1-Weekly Games'!$B:$B),2),MATCH($A4,INDEX('1-Weekly Games'!$C:$C,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$C:$C,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0),1),CHOOSE(--(INDEX(INDEX('1-Weekly Games'!$I:$J,MATCH($B4,'1-Weekly Games'!$B:$B,0),1):INDEX('1-Weekly Games'!$I:$J,MATCH($B4+1,'1-Weekly Games'!$B:$B),2),MATCH($A4,INDEX('1-Weekly Games'!$E:$E,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$E:$E,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0),1)="W")+1,"W","L")))

Single-valued I4 formula (zeroes changed to 2)...

IF($A4="","",IF(ISNUMBER(MATCH($A4,INDEX('1-Weekly Games'!$C:$C,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$C:$C,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0)),INDEX(INDEX('1-Weekly Games'!$I:$J,MATCH($B4,'1-Weekly Games'!$B:$B,0),1):INDEX('1-Weekly Games'!$I:$J,MATCH($B4+1,'1-Weekly Games'!$B:$B),2),MATCH($A4,INDEX('1-Weekly Games'!$C:$C,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$C:$C,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0),2),CHOOSE(--(INDEX(INDEX('1-Weekly Games'!$I:$J,MATCH($B4,'1-Weekly Games'!$B:$B,0),1):INDEX('1-Weekly Games'!$I:$J,MATCH($B4+1,'1-Weekly Games'!$B:$B),2),MATCH($A4,INDEX('1-Weekly Games'!$E:$E,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$E:$E,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0),2)="W")+1,"W","L")))

Single-valued H4 formula that can be filled across to column I (zeroes changed to COLUMN(H4)-COLUMN($H4)+1)...

IF($A4="","",IF(ISNUMBER(MATCH($A4,INDEX('1-Weekly Games'!$C:$C,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$C:$C,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0)),INDEX(INDEX('1-Weekly Games'!$I:$J,MATCH($B4,'1-Weekly Games'!$B:$B,0),1):INDEX('1-Weekly Games'!$I:$J,MATCH($B4+1,'1-Weekly Games'!$B:$B),2),MATCH($A4,INDEX('1-Weekly Games'!$C:$C,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$C:$C,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0),COLUMN(H4)-COLUMN($H4)+1),CHOOSE(--(INDEX(INDEX('1-Weekly Games'!$I:$J,MATCH($B4,'1-Weekly Games'!$B:$B,0),1):INDEX('1-Weekly Games'!$I:$J,MATCH($B4+1,'1-Weekly Games'!$B:$B),2),MATCH($A4,INDEX('1-Weekly Games'!$E:$E,MATCH($B4,'1-Weekly Games'!$B:$B,0)):INDEX('1-Weekly Games'!$E:$E,MATCH($B4+1,'1-Weekly Games'!$B:$B)),0),COLUMN(H4)-COLUMN($H4)+1)="W")+1,"W","L")))

Beau Nydal