By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Organise Data in Excel

P: 1
Hi. I have a bit of an issue in a sheet I'm trying to create and have no Idea really where to start.

Basically I currently have a form that inputs data into my s/sheet 'Data'.

I require a button that will produce a summary (possibly message box) of the details that are in the spreadsheet in rows A,B and C. I want the form to display each of the rows that are unique only. ie.


EY18 12-Feb Y-J
EY12 12-Feb Y-J
EY20 12-Feb Y-j
EY18 12-Feb J-F
EY12 12-Feb J-F
EY20 12-Feb J-F
EY18 12-Feb Y-J
EY12 12-Feb Y-J
EY20 12-Feb Y-j
EY18 12-Feb J-F
EY12 12-Feb J-F
EY20 12-Feb J-F
EY18 12-Feb Y-J
EY12 12-Feb Y-J
EY20 12-Feb Y-j
EY18 12-Feb J-F
EY12 12-Feb J-F
EY20 12-Feb J-F

Would Return:


EY18 12-Feb Y-J
EY12 12-Feb Y-J
EY20 12-Feb Y-j
EY18 12-Feb J-F
EY12 12-Feb J-F
EY20 12-Feb J-F



Any help at all would be much appreciated.

Paul
Feb 13 '12 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,366
You don't need VBA, Excel has built in functionality to remove duplicates.
Feb 13 '12 #2

NeoPa
Expert Mod 15k+
P: 31,494
There's certainly a Subtotals... option from the Data menu, but that wants something to aggregate. It also requires the data to be sorted I believe, which is also readily available from the Data menu.
Feb 13 '12 #3

Guido Geurs
Expert 100+
P: 767
This will search the unique data in Col "A" and set it in a MSGbox and dump it in Col"c".

Expand|Select|Wrap|Line Numbers
  1. Sub Search_Unique()
  2. Dim ARRDATA() As Variant
  3. Dim ARRDATAidx As Integer
  4. Dim ARRUNIQUE() As Variant
  5. Dim ARRUNIQUEidx As Variant
  6. Dim FOUND As Boolean
  7. Dim TEXTmsgbox As String
  8. ' find unique
  9.     ' put sheet in array
  10.     ARRDATA = Range("A1").Resize(Range("A1").End(xlDown).Row, 1)
  11.     ' dim ARRUNIQUE
  12.     ReDim ARRUNIQUE(0)
  13.     ' search through data
  14.     For ARRDATAidx = LBound(ARRDATA) To UBound(ARRDATA)
  15.         FOUND = False
  16.         ' search through ARRUNIQUE
  17.         For ARRUNIQUEidx = LBound(ARRUNIQUE) To UBound(ARRUNIQUE)
  18.             If ARRUNIQUE(ARRUNIQUEidx) = ARRDATA(ARRDATAidx, 1) Then FOUND = True
  19.         Next
  20.         ' if not in ARRUNIQUE then add
  21.         If FOUND = False Then
  22.             ReDim Preserve ARRUNIQUE(UBound(ARRUNIQUE) + 1)
  23.             ARRUNIQUE(UBound(ARRUNIQUE)) = ARRDATA(ARRDATAidx, 1)
  24.         End If
  25.     Next
  26. ' send msgbox
  27.     ' set ARRUNIQUE in text string
  28.     For ARRUNIQUEidx = LBound(ARRUNIQUE) To UBound(ARRUNIQUE)
  29.         TEXTmsgbox = TEXTmsgbox & ARRUNIQUE(ARRUNIQUEidx) & vbNewLine
  30.     Next
  31.     ' show MSGbox
  32.     MsgBox TEXTmsgbox
  33. ' dump unique
  34.     ' set ARRUNIQUE in a 2D array = ARRDATA
  35.     ' clean ARRDATA
  36.     ReDim ARRDATA(1 To UBound(ARRUNIQUE), 1 To 1)
  37.     ' transfer data
  38.     For ARRUNIQUEidx = 1 To UBound(ARRUNIQUE)
  39.         ARRDATA((ARRUNIQUEidx), 1) = ARRUNIQUE(ARRUNIQUEidx)
  40.     Next
  41.     ' dump data in sheet "C1"
  42.     Range("C1").Resize(UBound(ARRDATA, 1), 1) = ARRDATA
  43. End Sub
  44.  
Feb 15 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.