Paste unique values?

PROV226RV

New Member
Joined
Apr 1, 2013
Messages
31
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 A150.00
STORE A-2.65
STORE A50.00
STORE A-8.35
STORE B24.65
STORE B-9.45
STORE C87.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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
ADVERTISEMENT
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))
 
Upvote 0
Though you haven't asked for it, here's a VBA solution to complement the clever solutions already given:

Code:
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
 
Upvote 0
ADVERTISEMENT
maybe something a little simplier....

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

this assumes your table starts in A1 on sheet1
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,196,206
Messages
6,014,009
Members
441,802
Latest member
Aneurysm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top