List differences

[sparx]

Hello all, I have a form that works like a quote document and a macro
that copies all the quote detail to a worksheet - this worksheet
consists of columns of data, date, quote ref, materials, etc etc. One
column is a commodity reference for expendiature that is made up of 3
numbers or could be text - ie SPIPE or 043 could be Steel Pipe - these
numbers could appear loads of times or not at all and could be in any
order - starting in column Z ( Z3 ) and working down. I want to create
a list in any other column that looks down column Z3 to Z5002 and lists
each reference in turn - not the number of times say 043 appears but
will create a column say Y3=033 Y4=034 Y5=043 Y6=833 Y7=982 and so on
but in ascending order. If there are text references then they should
be listed too. I dont know if a macro could do this sort of thing or if
formula's need to be used.

 

[Otto Moehrbach]

A VBA macro can certainly do it but you can also do it manually. Post back
if you want a VBA solution.
For the manual way, do this.
Say the source column is Z.
Copy all the data in Column Z and paste it into Column AA.
Make sure Column AA has a header.
Select all the data in Column AA, header and all.
Click on Data - Filter - Advanced Filter.
Select "Filter the list in place."
Select "Unique records only."
Click OK.
Select all the visible occupied cells in Column AA.
Do Edit - Copy
Select cell AB1 and paste.
Click on Data - Filter - Show All.
Delete Column AA.
Done
HTH Otto

 

[sparx]

Thanks - if this can be done by vba then the better - only one problem
though - if you use the filters in excel - doesnt this cause all other
information to be removed permanently - or is all still intact - I
normally define ranges and use the match and index options but this
normally has to search against a specific item - i want the macro or
formula to look at the column with no user involvement and produce a
list in a separate column of all the different items in the first list.
I thank you for any help you can provide - just to let you know, I have
managed to to it but its via formula's and adds approx 3Mb to my file
that I can do without.

 

[Otto Moehrbach]

Use of the filter to filter the data does not remove anything, permanently
or otherwise. It just displays what you want to see and hides the rest.
When you do "Show All" it's all visible again.
If you want the procedure I gave you in a VBA macro, just record a macro
while you do it all manually.
If recording a macro does not do it the way you want, post back and tell
me what you didn't like in the recorded macro, and I'll write up some code
like you want. HTH Otto

 

[sparx]

Hello, thanks for the reply - I have attached an example excel file that
you can see what I am trying to achieve - basically, there is one great
long list of numbers that repeat over and over and numbers that are in
the list only once - my objective is to create a new list of numbers
from the great long list but to only list what ever number is in the
great long list only once so I can then define a name to the new
shorter list thats in an ascending order and indirectly add those
numbers to an index formula matching the defined number from the list.
Thats it really. I have managed to do what I mentioned above usinf
formula's but adds approx 3Mb - so am looking for an alternative macro.


+-------------------------------------------------------------------+
|Filename: example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4640 |
+-------------------------------------------------------------------+

 

[Guest]

Here's a play using non-array formulas ..

For numbers only comm codes in col F:

Placed in L3, copied down to cover max expected extent of data in col F:
=IF(F3="","",IF(COUNTIF($F$3:F3,F3)>1,"",F3+ROW()/10^10))

Then placed in M3, copied down to say, M30
(copy down just enough to cover the max expected number of items)
=IF(ISERROR(SMALL(L:L,ROW(A1))),"",INDEX(F:F,MATCH(SMALL(L:L,ROW(A1)),L:L,0)))

M3:M30 auto-returns a full ascending sort of the unique numbers within col F

Then just create a dynamic range for the DV: Numbers
=OFFSET(Sheet1!$M$3,,,SUMPRODUCT(--(Sheet1!$M$3:$M$30<>"")))

Similarly, for the text n numbers comm codes in col J:

Placed in N3, copied down to cover max expected extent of data in col J
=IF(J3="","",IF(COUNTIF($J$3:J3,J3)>1,"",IF(ISNUMBER(J3+0),J3+ROW()/10^10,ROW()*10^10)))

Then placed in O3, copied down to say, O30
(copy down just enough to cover the max expected number of items)
=IF(ISERROR(SMALL(N:N,ROW(A1))),"",INDEX(J:J,MATCH(SMALL(N:N,ROW(A1)),N:N,0)))

O3:O30 auto-returns a full ascending sort of the unique numbers & text
within col J,
with numbers sorted above text. Text items will appear in the "as-is"
sequence, i.e. w/o alpha sorting.

Then create a dynamic range for the DV: TextnNum
=OFFSET(Sheet1!$O$3,,,SUMPRODUCT(--(Sheet1!$O$3:$O$30<>"")))

Note: L1:O3 to be left empty

Examples of the 2 DVs created using Numbers and TextnNum are in cells P3 and
Q3

---

 

[Guest]

An implemented sample is at:
http://cjoint.com/?eqvkr34kj0
sparx_example.xls


---

 

[Guest]

Typo, line:

Note: L1:O3 to be left empty

should read as:

Note: L1:O2 to be left empty

---

 

[sparx]

I thank you so much - I have downloaded your file and can see it working
- With your permission, I will use your method as I believe its much
more efficient than what I came up with. Again, thank you.

 

[Max]

You're welcome, sparx !
Glad it helped ..

 

[sparx]

Your method saved me approx 2Mb within my file - it works exactly how I
needed it - again thank you.

 

[Max]

Your method saved me approx 2Mb within my file
- it works exactly how I needed it - again thank you.

Delighted to hear that !
Thanks for the feedback <g>

---