#1
|
|||
|
|||
PLEASE HELP: Excel data transformation
Guys,
i need to essentially switch data format from long to wide. In the simplest example let's consider a battleships game. Column A - Rows. A2 = 2 Column B - Columns. B2 = 6 Column C - Values. C2 = x on another sheet I have an 11X11 table, where columns and rows have numbers from 1 to 10. I want to fill up this table with formulas, so that "x" will appear in a cell corresponding to 2nd row and 6th column, all others keeping empty. moreover, I'd like to add new data on the first table: e.g. A3 = 3, B3 = 6, C3 = y, so that y will appear in the second table as well with accordance to these coordinates. do not suggest pivot tables and VBA please. It is a formula-based task I have now and it is much more complicated than what I have described, but please, I hope you could give me some help. |
#2
|
|||
|
|||
Is it something like the attached?
|
#3
|
|||
|
|||
Kind of, but it has to appear at the same 10x10 table, not two different ones.
And I should be able to extent the table (on sheet1) to add more and more items (up to 100 obviously to fill the whole 10x10 table). I tried to add an auxiliary column on sheet1 (Order column - 1,2,3 etc.) and for the table to use: IF ( MATCH(by row) = MATCH (by column) , VLOOKUP ( MATCH(by row) , Order column : values , 4 , false ) , "") But this is not working properly when there were multiple valus for the same column but different rows (only first one was accepted). |
#4
|
|||
|
|||
Deleted again.
Last edited by xor; 07-27-2017 at 10:48 AM. |
#5
|
|||
|
|||
See the attached.
|
#6
|
|||
|
|||
Thank you!!! That was exactly it! Strongly appreciated!
And Could you please explain the formula: why are you using OR operator there? |
#7
|
|||
|
|||
See the attached.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word & Excel 2010 - Best Options To Auto Insert MySQL & Excel Data? | Hoser | Word | 1 | 03-17-2017 03:47 PM |
Embedded Excel charts refresh only data values, not which data points (rows) are included | GregL | Word | 0 | 12-01-2016 10:24 AM |
use existing table data and not get generic excel chart data | canar | Word Tables | 3 | 09-08-2015 03:36 PM |
How to replicate, consolidate and count cell data in excel for data consoldation. | Loran | Excel | 7 | 06-05-2014 01:07 AM |
[Excel 2007] Building Power Point Slides from data in an Excel Table | bremen22 | Excel Programming | 1 | 08-07-2013 11:01 AM |