Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column to row
Greetings Fellow 4th of July Workers!
I was wondering if someone could help me move a column with a like code to column C: Any help would be great! I have a spreadsheet like this: A B 789 Restaurant 661 Restaurant 999 Restaurant 789 Tasty 100 Restaurant So that the result would be A B C 789 Restaurant Tasty |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column to row
Say your data is in A2:B11. Output starts at D2. In D2:
=A2 In D3 the following *array* formula (commit with Shift+Ctrl+Enter): =IF(ISERROR(MATCH(0,COUNTIF($D$2:D2,$A$2:$A$11),0) ),"",INDEX($A$2:$A $11,MATCH(0,COUNTIF($D$2:D2,$A$2:$A$11),0))) You can copy down D3 as far as needed, until you get blanks. Now in E2 (*array* formula): =IF(COLUMNS($E$2:E2)<=COUNTIF($A$2:$A$11,$D2),INDE X($B$2:$B $11,SMALL(IF($A$2:$A$11=$D2,ROW($A$2:$A$11)-ROW($A$2)+1),COLUMNS($E $2:E2))),"") You can copy this formula down and across as far as necessary. HTH Kostis Vezerides On Jul 4, 3:28 pm, Outlook, eh? wrote: Greetings Fellow 4th of July Workers! I was wondering if someone could help me move a column with a like code to column C: Any help would be great! I have a spreadsheet like this: A B 789 Restaurant 661 Restaurant 999 Restaurant 789 Tasty 100 Restaurant So that the result would be A B C 789 Restaurant Tasty |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column to row
Outlook wrote:
Greetings Fellow 4th of July Workers! I was wondering if someone could help me move a column with a like code to column C: Any help would be great! I have a spreadsheet like this: A B 789 Restaurant 661 Restaurant 999 Restaurant 789 Tasty 100 Restaurant So that the result would be A B C 789 Restaurant Tasty If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, with your lookup value (789 in your illustration) in D2, array enter into E2:F2 =TRANSPOSE(VLookups(D2, A1:B5, 2)) Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
formula : =(column A)+(column B)-(column C). Why won't it work? | Excel Discussion (Misc queries) |