Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
=IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(SUBTOTAL(2, OFFSET($C$11:$C$16,ROW($C$11:$C$16)-ROW($C$11),,1)), $C$11:$C$16),ROWS($A$3:A3)),"") I need to alter the above formula to remove the duplicates and list the top (five) highest unique records only. The table is already in auto filter mode. Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
In article ,
MP wrote: =IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(SUBTOTAL(2, OFFSET($C$11:$C$16,ROW($C$11:$C$16)-ROW($C$11),,1)), $C$11:$C$16),ROWS($A$3:A3)),"") I need to alter the above formula to remove the duplicates and list the top (five) highest unique records only. The table is already in auto filter mode. Thanks, Try... =IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(ISNA(MATCH($C$11: $C$16,$A$2:A2,0)),IF(SU BTOTAL(2,OFFSET($C$11:$C$16,ROW($C$11:$C$16)-ROW($C$11),,1)),$C$11:$C$16) ),ROWS($A$3:A3)),"") ....confirmed with CONTROL+SHIFT+ENTER. Then, I think, the formula for A1 needs to change as well. Hope this helps! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
Thanks for the reply however i could not get the formula to work. Maybe i
missed something trying to adjust the formula per the data in my table. =IF(ROWS($C$2:C2)<=$C$1,LARGE(IF(ISNA(MATCH($D$2:$ D$2042,$C$1:C1,0)),IF(SUBTOTAL(2,OFFSET($D$2:$D$20 42,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042)),ROWS($C$2:C2)),"") Column C: Product Name Column D: Value Thanks for your assistance "Domenic" wrote: In article , MP wrote: =IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(SUBTOTAL(2, OFFSET($C$11:$C$16,ROW($C$11:$C$16)-ROW($C$11),,1)), $C$11:$C$16),ROWS($A$3:A3)),"") I need to alter the above formula to remove the duplicates and list the top (five) highest unique records only. The table is already in auto filter mode. Thanks, Try... =IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(ISNA(MATCH($C$11: $C$16,$A$2:A2,0)),IF(SUBTOTAL(2,OFFSET($C$11:$C$16 ,ROW($C$11:$C$16)-ROW($C$11),,1)),$C$11:$C$16)),ROWS($A$3:A3)),"") ....confirmed with CONTROL+SHIFT+ENTER. Then, I think, the formula for A1 needs to change as well. Hope this helps! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
Sorry, my mistake! Try replacing the last instance of...
ROWS($C$2:C2) with 1 Hope this helps! In article , MP wrote: Thanks for the reply however i could not get the formula to work. Maybe i missed something trying to adjust the formula per the data in my table. =IF(ROWS($C$2:C2)<=$C$1,LARGE(IF(ISNA(MATCH($D$2:$ D$2042,$C$1:C1,0)),IF(SUBTOT AL(2,OFFSET($D$2:$D$2042,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042)),ROWS( $C$2:C2)),"") Column C: Product Name Column D: Value Thanks for your assistance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
I changed the last instance and could not get the fomula to work properly.
Still list the duplicate values. I appreciate your help! "Domenic" wrote: Sorry, my mistake! Try replacing the last instance of... ROWS($C$2:C2) with 1 Hope this helps! In article , MP wrote: Thanks for the reply however i could not get the formula to work. Maybe i missed something trying to adjust the formula per the data in my table. =IF(ROWS($C$2:C2)<=$C$1,LARGE(IF(ISNA(MATCH($D$2:$ D$2042,$C$1:C1,0)),IF(SUBTOT AL(2,OFFSET($D$2:$D$2042,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042)),ROWS( $C$2:C2)),"") Column C: Product Name Column D: Value Thanks for your assistance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
Can you post the formula contained in C1? Also, can you confirm which
cells and worksheet contain the data, and which cells and worksheet contain the formulas? In article , MP wrote: I changed the last instance and could not get the fomula to work properly. Still list the duplicate values. I appreciate your help! "Domenic" wrote: Sorry, my mistake! Try replacing the last instance of... ROWS($C$2:C2) with 1 Hope this helps! In article , MP wrote: Thanks for the reply however i could not get the formula to work. Maybe i missed something trying to adjust the formula per the data in my table. =IF(ROWS($C$2:C2)<=$C$1,LARGE(IF(ISNA(MATCH($D$2:$ D$2042,$C$1:C1,0)),IF(SU BTOT AL(2,OFFSET($D$2:$D$2042,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042)),R OWS( $C$2:C2)),"") Column C: Product Name Column D: Value Thanks for your assistance |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
Sure, No formula in C1 just the filtered title "Products".
C2:C2042 contain various product names D2:D2042 contain various product values both are on the same worksheet. Sheet 1 "ProdData" "Domenic" wrote: Can you post the formula contained in C1? Also, can you confirm which cells and worksheet contain the data, and which cells and worksheet contain the formulas? In article , MP wrote: I changed the last instance and could not get the fomula to work properly. Still list the duplicate values. I appreciate your help! "Domenic" wrote: Sorry, my mistake! Try replacing the last instance of... ROWS($C$2:C2) with 1 Hope this helps! In article , MP wrote: Thanks for the reply however i could not get the formula to work. Maybe i missed something trying to adjust the formula per the data in my table. =IF(ROWS($C$2:C2)<=$C$1,LARGE(IF(ISNA(MATCH($D$2:$ D$2042,$C$1:C1,0)),IF(SU BTOT AL(2,OFFSET($D$2:$D$2042,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042)),R OWS( $C$2:C2)),"") Column C: Product Name Column D: Value Thanks for your assistance |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
Since you haven't provided me with the cell references and the name of
the worksheet containing the formulas returning the results, try the following.... On a separate sheet... B2, confirmed with CONTROL+SHIFT+ENTER: =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(Sheet1!D2:D 2042,ROW(Sheet1!D2:D204 2)-ROW(Sheet1!D2),0,1)),Sheet1!D2:D2042),IF(SUBTOTAL( 2,OFFSET(Sheet1!D2:D 2042,ROW(Sheet1!D2:D2042)-ROW(Sheet1!D2),0,1)),Sheet1!D2:D2042)),1)) or =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(Sheet1!D2:D 2042,ROW(Sheet1!D2:D204 2)-ROW(Sheet1!D2),0,1)),Sheet1!D2:D2042),Sheet1!D2:D2 042),1)) C2, confirmed with CONTROL+SHIFT+ENTER, and copied down: =IF(ROWS(C$2:C2)<=$B$2,LARGE(IF(ISNA(MATCH(Sheet1! $D$2:$D$2042,$C$1:C1,0) ),IF(SUBTOTAL(2,OFFSET(Sheet1!$D$2:$D$2042,ROW(She et1!$D$2:$D$2042)-ROW(S heet1!$D$2),,1)),Sheet1!$D$2:$D$2042)),1),"") Hope this helps! In article , MP wrote: Sure, No formula in C1 just the filtered title "Products". C2:C2042 contain various product names D2:D2042 contain various product values both are on the same worksheet. Sheet 1 "ProdData" |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
Hopefully this will give you alittle more info (table with desired results)
Sheet 1 Name: "Data1" COLUMN A COLUMN B Item Name Item Amount Top Values Construction 90 143 Products 143 Desired Result 142 Products 62 list top 5 Unique Col B values 140 Products 143 (No Duplicates) 131 Products 62 111 Products 71 Products 62 Products 117 Products 140 Products 94 GL 131 GL 103 MFG 142 GL 107 Products 121 Construction 62 Top five value can be listed on Column D in sheet 1("Data1") or if need be on another sheet 2("Data2") "Domenic" wrote: Since you haven't provided me with the cell references and the name of the worksheet containing the formulas returning the results, try the following.... On a separate sheet... B2, confirmed with CONTROL+SHIFT+ENTER: =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(Sheet1!D2:D 2042,ROW(Sheet1!D2:D204 2)-ROW(Sheet1!D2),0,1)),Sheet1!D2:D2042),IF(SUBTOTAL( 2,OFFSET(Sheet1!D2:D 2042,ROW(Sheet1!D2:D2042)-ROW(Sheet1!D2),0,1)),Sheet1!D2:D2042)),1)) or =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(Sheet1!D2:D 2042,ROW(Sheet1!D2:D204 2)-ROW(Sheet1!D2),0,1)),Sheet1!D2:D2042),Sheet1!D2:D2 042),1)) C2, confirmed with CONTROL+SHIFT+ENTER, and copied down: =IF(ROWS(C$2:C2)<=$B$2,LARGE(IF(ISNA(MATCH(Sheet1! $D$2:$D$2042,$C$1:C1,0) ),IF(SUBTOTAL(2,OFFSET(Sheet1!$D$2:$D$2042,ROW(She et1!$D$2:$D$2042)-ROW(S heet1!$D$2),,1)),Sheet1!$D$2:$D$2042)),1),"") Hope this helps! In article , MP wrote: Sure, No formula in C1 just the filtered title "Products". C2:C2042 contain various product names D2:D2042 contain various product values both are on the same worksheet. Sheet 1 "ProdData" |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records formula
Try...
Data2!D2, confirmed with CONTROL+SHIFT+ENTER, copied down to D6: =LARGE(IF(ISNA(MATCH(Data1!$B$2:$B$2042,Data2!$D$1 :D1,0)),IF(SUBTOTAL(2,O FFSET(Data1!$B$2:$B$2042,ROW(Data1!$B$2:$B$2042)-ROW(Data1!$B$2),,1)),Dat a1!$B$2:$B$2042)),1) ....which will return the following results... 143 142 140 131 121 I'm assuming that in your results you meant 121 as the fifth unique value, not 111. Also, note that if you have the results on the same worksheet as the source data, the formulas should be entered either before or after the data, not beside it. Otherwise some of the results will be hidden when the data is filtered. Hope this helps! In article , MP wrote: Hopefully this will give you alittle more info (table with desired results) Sheet 1 Name: "Data1" COLUMN A COLUMN B Item Name Item Amount Top Values Construction 90 143 Products 143 Desired Result 142 Products 62 list top 5 Unique Col B values 140 Products 143 (No Duplicates) 131 Products 62 111 Products 71 Products 62 Products 117 Products 140 Products 94 GL 131 GL 103 MFG 142 GL 107 Products 121 Construction 62 Top five value can be listed on Column D in sheet 1("Data1") or if need be on another sheet 2("Data2") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique records in pivot table - formula? | Excel Discussion (Misc queries) | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) | |||
Extracting unique records by formula | Excel Discussion (Misc queries) | |||
Unique Records | Excel Discussion (Misc queries) | |||
unique records | Excel Worksheet Functions |