Paste unique values?

[PROV226RV]

I need a formula that will allow me to reference a list of store names ONCE in another column but not paste duplicates. So for example, I have a list of invoices from stores
on one sheet. I need to reference these same stores and reflect store totals on another sheet based on the information from the first sheet but I only need to list each store once. See example below

SHEET 1

COLUMN A (STORE)	COLUMN B (INVOICE AMOUNT)
STORE A	150.00
STORE A	-2.65
STORE A	50.00
STORE A	-8.35
STORE B	24.65
STORE B	-9.45
STORE C	87.00
STORE C	-4.50

<tbody>
</tbody>

SHEET 2

COLUMN A (STORE)	COLUMN B (TOTALS)
STORE A	$189.00
STORE B	$15.20
STORE C	$82.50

<tbody>
</tbody>

Thanks for any help.

Rachel V in Florida

 

[nuked]

Select your data.
Click 'Data' tab
Click 'Advanced' next to Filter
Select 'copy to another location'
Click 'Unique Records Only'.
Choose your destination in the 'copy to' box.

 

[etaf]

Assuming the store info is in sheet1 starting at row A2 to a400

=INDEX(Sheet1!$A$2:$A$400, MATCH(0, COUNTIF($A1:A$1, Sheet1!$A$2:$A$400), 0))

Put this into sheet2 cell A2 and enter as an array formula - while in the formula bar - use Control+shift+Enter Key
to get the {} brackets around the forumla and copy down

that will extract all the unique values into the new sheet for the store

now in sheet2 cell B2
assuming the Values are in sheet1 Column B
=SUMIF(Sheet1!$A$2:$A$400,A2,sheet1!$B$2:$B$400)

this will look at sheet2 cell A2 for the store ID and then use that as a criteria on sheet1 Column A and then sum the values for that store from column B
and return the results into sheet2

 

[miss_ell]

Hello,

An easy way to do this is to create a pivot table.

Click inside the table then choose Data, Pivot Table and Chart Report, Next, Next, then Finish. The new report should be in a different worksheet. Drag the store heading into the Column space and the invoice amount into the Data space.

You should have the information you require, but if not, let me know.

 

[etaf]

correction to the formula as i added $ to the wrong cells - sorry about that - a Pivot table would be much cleaner as suggested by miss_ell

=INDEX(Sheet1!A2:A400, MATCH(0, COUNTIF($A$1:A1, Sheet1!A2:A400), 0))

 

[Trebor76]

Though you haven't asked for it, here's a VBA solution to complement the clever solutions already given:

Option Explicit
Sub Macro1()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au

    Dim rngCell As Range
    Dim objMyUniqueList As Object
    Dim intItemCounter As Integer
    Dim varItem As Variant
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    Set objMyUniqueList = CreateObject("Scripting.Dictionary")
    
    For Each rngCell In Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
    
        If objMyUniqueList.exists(CStr(rngCell)) = False Then
            intItemCounter = intItemCounter + 1
            objMyUniqueList.Add CStr(rngCell), intItemCounter
        End If
    
    Next rngCell
    
    For Each varItem In objMyUniqueList
    
        If WorksheetFunction.CountA(Sheets("Sheet2").Cells) = 0 Then
            lngMyRow = 2 'Default row number if there's no data in Sheet2. Change to suit.
        Else
            lngMyRow = Sheets("Sheet2").Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        End If
    
        With Sheets("Sheet2")
            .Range("A" & lngMyRow).Value = varItem
            .Range("B" & lngMyRow).Value = Evaluate("SUMIF(Sheet1!A:A,A" & lngMyRow & ",Sheet1!B:B)")
        End With
            
    Next varItem
    
    Set objMyUniqueList = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "Summary has now been prepared"

End Sub

Regards,

Robert

 

[Weazel]

maybe something a little simplier....

=SUMIF(Sheet1!A2:A9,A2,Sheet11!B2:B9)

this assumes your table starts in A1 on sheet1

 

[PROV226RV]

I think this formula is what I am looking for. I tried Nuked's idea which looked like the simplest but I have formatted my sheet where all columns have a width of 2 (sort of like graph paper) and have merged cells together because I have a range of info on the sheet in many different places and I need my columns to be as such. (I am not willing to change my formatting either) soooooooo excel doesn't like nuked's idea because I have merged cells. I tried copying them to another part of the same sheet in one cell (not merged), same problem. I wish it would work though. Sounds simple enough.

My question now is, what is $a1:$a1? My exact references would be this:

=+INDEX(M!$F$2:$K$24, MATCH(0,COUNTIF( what goes here, M!$F$2:$K$24 ),0))

When i entered it with a1:a1, it returned a N/A value on the other sheet. I changed it to f1:f1 since that is the header for "Store" on that sheet? is that what it is supposed to look for? I have a bunch of other data below this set of cells so I really only need it to reference from f2:k24. I really don't feel like typing each store name on the other sheet. I have 6 days to track, hence 6 separate sheets with a base summary sheet for each one to refer back to so 12 total. I really need a formula to work for me so I have a base template to work from week to week.

Assuming the store info is in sheet1 starting at row A2 to a400

=INDEX(Sheet1!$A$2:$A$400, MATCH(0, COUNTIF($A1:A$1, Sheet1!$A$2:$A$400), 0))

Put this into sheet2 cell A2 and enter as an array formula - while in the formula bar - use Control+shift+Enter Key
to get the {} brackets around the forumla and copy down

that will extract all the unique values into the new sheet for the store

now in sheet2 cell B2
assuming the Values are in sheet1 Column B
=SUMIF(Sheet1!$A$2:$A$400,A2,sheet1!$B$2:$B$400)

this will look at sheet2 cell A2 for the store ID and then use that as a criteria on sheet1 Column A and then sum the values for that store from column B
and return the results into sheet2

 

[etaf]

see my correct formula above

=+INDEX(M!$F$2:$K$24, MATCH(0,COUNTIF( what goes here, M!$F$2:$K$24 ),0))

=INDEX(M!F2:F24, MATCH(0,COUNTIF( the column you are in from row 1 M!F2:F24 ),0))
you only reference the column with the stores in - and so if you are entering the formula into column A then use
$A$1:A1
column F then use
$F$1:F1

dont forget to need to use control+shift+enter to get an array {} formula

 

[PROV226RV]

I tried it and it didn't work so I went on the hunt for more solutions online. This is what I found that worked:
[SOLVED] formula to create list of unique items scroll down to JBeaucaire's response --

{Two formulas together will do this, and both are array formulas. Let's assume that list is in column A.

Over in another column, let's use M, enter this formula in M1:

=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))
...and press CTRL-SHIFT-ENTER to activate an array in that cell, ENTER alone will not work.

Then in M2 enter this array formula:

=IF(COUNTIF($A$1:$A$1000,">"&M1),INDEX($A$1:$A$1000,MATCH(COUNTIF($A$1:$A$1000,"<="&M1),COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0)),"")
...and press CTRL-SHIFT-ENTER to activate an array in that cell, ENTER alone will not work.

Now, copy that second formula down as many cells as necessary to get the full list to appear, and maybe some extras for good measure.

NOTE: Side effect will also be the list created will be alphabetized.}


Thanks to everyone who tried to help me.

Rachel V. in Florida