Search Results

Search found 3 results on 1 pages for 'polarbear2k'.

Page 1/1 | 1 

• Transpose matrix-style table to 3 columns in Excel

  - by polarbear2k

  I have a matrix-style table in excel where B1:Z1 are column headings and A2:A99 are row headings. I would like to convert this table to a 3 column table (column heading, row heading, cell value). It does not matter in what order the new table is. A B C D A B C A B C 1 H1 H2 H3 1 H1 R1 V1 1 H1 R1 V1 2 R1 V1 V2 V3 => 2 H1 R2 V4 or 2 H2 R1 V2 3 R2 V4 V5 V6 3 H1 R3 V7 3 H3 R1 V3 4 R3 V7 V8 V9 4 H2 R1 V2 4 H1 R2 V4 5 H2 R2 V5 5 H2 R2 V5 6 H2 R3 V8 6 H3 R2 V6 7 H3 R1 V3 7 H1 R3 V7 8 H3 R2 V6 8 H2 R3 V8 9 H3 R3 V9 9 H3 R3 V8 I've been playing around with the OFFSET function to create the whole table but I feel like a combination of TRANSPOSE and V/HLOOKUP is required. Thanks EDIT I have managed to come up with the correct formulas. If the data is in Sheet1 like in my example above, the formulas go in Sheet2: [A1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99), OFFSET(Sheet1!$A$1,0,IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"") [B1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),0),"") [C1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"") The formulas are limited to B1:Z1 for the headings and A2:A99 for the rows (these can be increased to their maximums if required). The COUNTA() formula returns the number of cells that actually have values, which limits the number of rows returned to headings*rows. Otherwise the formulas would could go on for infinity because of the MOD function.

  Read the article

• Traspose matrix-style table to 3 columns in Excel

  - by polarbear2k

  I have a matrix-style table in excel where B1:Z1 are column headings and A2:A99 are row headings. I would like to convert this table to a 3 column table (column heading, row heading, cell value). It does not matter in what order the new table is. A B C D A B C A B C 1 H1 H2 H3 1 H1 R1 V1 1 H1 R1 V1 2 R1 V1 V2 V3 => 2 H1 R2 V4 or 2 H2 R1 V2 3 R2 V4 V5 V6 3 H1 R3 V7 3 H3 R1 V3 4 R3 V7 V8 V9 4 H2 R1 V2 4 H1 R2 V4 5 H2 R2 V5 5 H2 R2 V5 6 H2 R3 V8 6 H3 R2 V6 7 H3 R1 V3 7 H1 R3 V7 8 H3 R2 V6 8 H2 R3 V8 9 H3 R3 V9 9 H3 R3 V8 I've been playing around with the OFFSET function to create the whole table but I feel like a combination of TRANSPOSE and V/HLOOKUP is required. Thanks

  Read the article

• LINQ: GroupBy with maximum count in each group

  - by polarbear2k

  Hi, I have a list of duplicate numbers: Enumerable.Range(1,3).Select(o => Enumerable.Repeat(o, 3)).SelectMany(o => o) // {1,1,1,2,2,2,3,3,3} I group them and get quantity of occurance: Enumerable.Range(1,3).Select(o => Enumerable.Repeat(o, 3)).SelectMany(o => o) .GroupBy(o => o).Select(o => new { Qty = o.Count(), Num = o.Key }) Qty Num 3 1 3 2 3 3 What I really need is to limit the quantity per group to some number. If the limit is 2 the result for the above grouping would be: Qty Num 2 1 1 1 2 2 1 2 2 3 1 3 So, if Qty = 10 and limit is 4, the result is 3 rows (4, 4, 2). The Qty of each number is not equal like in example. The specified Qty limit is the same for whole list (doesn't differ based on number). Thanks

  Read the article

1