# Paste unique values?

#### PROV226RV

##### New Member
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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.

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:
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.

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))

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
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

maybe something a little simplier....

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

this assumes your table starts in A1 on sheet1

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

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

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

Replies
8
Views
179
Replies
8
Views
184
Replies
1
Views
860
Replies
0
Views
212
Replies
12
Views
1K

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?

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