I looked the function up but I can't make it work.
So in one tab (lets call it Tab1) I'll have a bunch of data and what I'm trying to do is to create on another one (Tab2) a list of all the uniques Approval #s I have on Tab1.
So for example Tab 1 Column B would have
1
1
1
1
2
2
2
3
3
3
4
4
and I want Tab 2 to pull that info and show it to me like this
1
2
3
4
There is a "Remove Duplicates" function built into Excel under the Data tab that will do this for you, or does it need to be done with a formula?
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
Hi
See the file!!
Regard
micope21
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
Is your REAL data numbers like you have posted?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
It has to be in a formula. One tab would be data dump, the other would work like a query basically.
Will there be gaps (blank cells) in the data and (to repeat Tony's question) are the real data numbers or text, or a mixture?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
No, I will probably have to post the real one.
All numbers, no blanks
Thank you so much micope21! I'm trying to adjust the formula to what I have
Micope21's reply should do the job for you, in that case.
Tab name Table, Column B
Approval #
3255695
3255695
3255695
3255695
3255856
3255856
3256069
3256069
3256069
3256069
3256069
3256195
3256195
3256195
3256195
3256241
3256241
3256241
3256241
3256241
3256241
3256241
3256307
3256307
And then on the next tab I would have the unique values listed. I tried to modify the other excel file but I lost myself after the countif. Is there a difference when it comes to the fact that this list will keep growing? So I would just have Index(Table!B:B) for ex
I guess it has to be limited...
I'd normally use a Table for the source info. then the code updates every time you add another row.
That should do it! How do you name column B as a Table though?
Try this Array Formula which requires confirmation with Ctrl+Shift+Enter.
On Sheet2
In G2
and then copy down until you get blank cells.
Please Login or Register to view this content.
Notice that the formula is starting from G2 hence the range which is referenced in Countif is the one cell above the first formula cell. So if you want to populate this unique list in say Col. M and starting from M5, the countif in the formula would be COUNTIF($M$4:M4, which is one cell above the first formula cell.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
To make a Table. Select anc cell inthe data block that you want to make into a table in Sheet 1. Then INSERT TABLE (ensuring you make the corrrect choice about header rows) and there it is...
Try this...
On some sheet...
Enter this formula in A2:
=MIN(Table!B2:B25)
Enter this array formula** in A3:
=IFERROR(1/(1/MIN(IF(Table!B$2:B$25>A2,Table!B$2:B$25))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks