Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique Records or Transpose Help
I have data in two columns that looks like Ref1 below. Column A has a value
repeated for however many times a value in column B shows up. I need unique records in column A so that it displays like Ref2 below but I don't care how many columns that converts to. Is there a way to do that without using VB? I can paste special transpose for a few records, but I have thousands? Ref1 ColA ColB 1 A 2 A 2 B 2 C 3 A 4 A 4 B 6 D 6 F 6 J Ref2 ColA ColB ColC ColD 1 A 2 A B C 3 A 4 A B 6 D F J Thanks in advance for the help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique Records or Transpose Help
On Tue, 28 Jul 2009 15:37:01 -0700, JKurt74
wrote: I have data in two columns that looks like Ref1 below. Column A has a value repeated for however many times a value in column B shows up. I need unique records in column A so that it displays like Ref2 below but I don't care how many columns that converts to. Is there a way to do that without using VB? I can paste special transpose for a few records, but I have thousands? Ref1 ColA ColB 1 A 2 A 2 B 2 C 3 A 4 A 4 B 6 D 6 F 6 J Ref2 ColA ColB ColC ColD 1 A 2 A B C 3 A 4 A B 6 D F J Thanks in advance for the help. Assuming your data (Ref1) is in Sheet1, you may try this in Sheet2: In cell A1 put =Sheet1!A1 In cell A2 put =INDEX(Sheet1!A$1:A$100,MATCH(TRUE,INDEX(ISNA(MATC H(Sheet1!A$1:A$100,A$1:A1,0)),0),0)) Copy cell A2 down as far as needed In cell B1 put =IF(COUNTIF(Sheet1!$A$1:$A$100,$A1)COLUMN()-2,INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Sheet1!$A$1:$A $100=$A1,ROW(Sheet1!$A$1:$A$100)),COLUMN()-1)),"") Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Change the 100 on all places to fit the size of your data on Sheet1 (Ref1) Copy cell B1 to the right as far as needed, e.g. to cell J1 Copy cells B1 to J1 down as far as needed. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique Records or Transpose Help
Excel 2007 Tables
With Structured References No VBA http://www.mediafire.com/file/ejommi...07_28_09a.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) | |||
Unique Records | Excel Discussion (Misc queries) | |||
Unique records | Excel Discussion (Misc queries) | |||
Transpose unique values in one column/mult. rows into a single row | Excel Worksheet Functions | |||
unique records | Excel Worksheet Functions |