Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merge Rows depending on value of the first column
Hi All, i have worksheet with the following format 1111 abc 1111 xyz 1111 x12 1234 qwe I need help to merge column b values depending on column A.If two rows have the same value in column a then i would like to append the value of the cells in column b to the first row as shown below. So i need the above data to appear as 1111 abc xyz x12 1234 qwe thanks sp123 -- sp123 ------------------------------------------------------------------------ sp123's Profile: http://www.excelforum.com/member.php...o&userid=31163 View this thread: http://www.excelforum.com/showthread...hreadid=534373 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merge Rows depending on value of the first column
Assume your data (with headers) looks like this:
Qty Lib1 An 100A An 101B An 102C An 103D An 104E Bn 105F Cn 106G Cn 107H Cn 108J Dn 109K Create the Lib2 list of unique items in Lib1 with Data Filter Advanced Filter Copy to another location Unique Records only Lib2 An 104E 103D 102C 101B 100A Bn 105F Cn 108J 107H 106G Dn 109K Name Lib1, Lib2, and Qty. The first cell of Qty contains a space. All formulas are in R1C1 ref style. Create these addittional names: bins Refers To: =ROW(INDEX(C1,1):INDEX(C1,ROWS(Lib1))) reps Refers To: =MAX(COUNTIF(Lib1,Lib1)) colu Refers To: =COLUMN(INDEX(R1,1):INDEX(R1,reps)) Select the 5 cells next to Lib2, An (5=reps=max repetitions in Lib1) and enter this array formula: =INDEX(Qty,LARGE(TRANSPOSE(IF(Lib1=Lib2 R,bins,0)),colu)+1) With the fill handle, drag this set of cells down to fill the array. Select A1 ref style to translate all to your preference. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |