Hi,
I love excell but am struggling with a macro. I hope someone can help.
I am trying to create a macro that will take the values of columnB, count the duplicates then display the values in columnE with the total number of value duplicates in ColumnF
I sourced this code which does something like I want but I cant split the Value B data(Code is A) into separate columns. I might be over thinking it. Code below.
Please Login or Register to view this content.
Last edited by paperbo; 01-11-2018 at 12:15 PM. Reason: Request to comply with rules
Important
- please click on edit post on your post and add code tags to your code by selecting the relevant text and then clicking on #
- this makes the code easier to read and complies with forum rules
What goes in column E? - is it the duplicate value?
What goes in F? - I am not sure what you are trying to split
What is in B?
Perhaps you could attach a workbook showing the original values and what you want in D & E
- click GoAdvanced
- look below and click on ManageAttachments
- follow screen instructions
Thanks
Last edited by kev_; 01-11-2018 at 11:09 AM.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
Hi,
Thanks for getting back to me so quickly. I hope this attached image explains it a bit better.
The image is how i want it to display after the macro is run.
Untitled.jpg
- range of values is attributed to column E, then sorted and duplicates removed
- column E contains result of CountIf function
- headers added
- run in attached workbook with {CTRL} k
Plesae add code tags to post#1 as requested in post#2Please Login or Register to view this content.
paperbo.xlsm
Last edited by kev_; 01-11-2018 at 11:50 AM.
You could also achieve this without VBA
In E4 copy down
Formula:
Please Login or Register to view this content.
In F4 copy down
Formula:Please Login or Register to view this content.
The 2nd formula lazily refers to whole of column B
- should be OK unless area above/below data in column B contains value matching a Barcode
Last edited by kev_; 01-11-2018 at 11:52 AM.
This sort of works but it generates an error.
please see image attached.
Untitled2.jpg
Hi,
This also partially works and may be a way forward. Clearing the blank cells with a macro removes the formula thought. The data from column E and F will be copied and pasted into another spreadsheet which can sort everything out using concatenation. I just want to tidy it up a bit before i copy and paste.
The VBA error is probably due to the your Excel version
- are you running Excel 2003?
- the remove duplicates function was probably introduced with Excel 2007
- let me know and I will revise the VBA tomorrow
Hi,Originally Posted by kev_
Yes, I’m on Excel 2003. It’s a massive help this, that will be used on a daily basis for my stock control system.
- I think this should work with Excel 2003
- Barcode count values placed in a new worksheet named "Unique"
- making it easy to delete that worksheet if you then copy them elsewhere
- run in attached workbook with {CTRL} k
How it works:
- sheet containing barcodes activated to set the correct range of values to use
- new worksheet added
- barcode values attributed to new sheet (more efficient than copy/paste) starting at A2
- new values sorted
- TWO counts calculated for each barcode (total and cumulative to each row)
- total count placed in columnB (BUT only against first occurence of barcode - ie when cumulative count = 1)
- rows where cell in columnB is BLANK deleted (identified using SpecialCells property)
Any questions?
- you must ask if there is anything in the code that you do not fully understand
- if you need further help in taking things to the next stage let me know
paperbo v2.xlsmPlease Login or Register to view this content.
- I think this should work with Excel 2003
- Barcode count values placed in a new worksheet named "Unique"
- making it easy to delete that worksheet if you then copy them elsewhere
- run in attached workbook with {CTRL} k
How it works:
- sheet containing barcodes activated to set the correct range of values to use
- new worksheet added
- barcode values attributed to new sheet (more efficient than copy/paste) starting at A2
- new values sorted
- TWO counts calculated for each barcode (total and cumulative to each row)
- total count placed in columnB (BUT only against first occurence of barcode - ie when cumulative count = 1)
- rows where cell in columnB is BLANK deleted (identified using SpecialCells property)
Any questions?
- you must ask if there is anything in the code that you do not fully understand
- if you need further help in taking things to the next stage let me know
Attachment 555771Please Login or Register to view this content.
You Sir, are an absolute genius ! It works perfectly and far better than I could get it. Placing the required output data on a different sheet is perfect although this data needs moving manually to a different workbook which is not a problem. Its in a nice tidy format and will work well once pasted into the correct location. We will be able to start implementing this system next week.
Just for your information, the document sits in Onedrive and is accessed via a Windows10 notebook. input is via a bluetooth barcode scanner. The data is fed in and then sorted. Once saved the document is then accessed by another computer/person and the output data in 'Unique' pasted into another workbook that controls stock levels. The data is then sorted again and applied via the bar code to the product.
I cant thank you enough.
Ray
That process could probably be automatedthis data needs moving manually to a different workbook
How is it incorporated in the other workbook?
- as a new workbook?
- into an existing workbook?
- into a new sheet?
- into an existing sheet?
Let me know what you are doing manually, and perhaps we can make ife easier
Another one.
Please Login or Register to view this content.
Last edited by bakerman2; 01-12-2018 at 08:10 PM.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Hi, I have just run this through a complete cycle: Barcode scanner>notebook>onedrive>copy and paste>final stock, and it works perfectly. The master sheet (it gets pasted into ) is not on onedrive as its a secure excel document. For the record, Column A and B in Unique, are pasted into and existing sheet in an existing workbook.That process could probably be automated
How is it incorporated in the other workbook?
- as a new workbook?
- into an existing workbook?
- into a new sheet?
- into an existing sheet?
Let me know what you are doing manually, and perhaps we can make ife easier
@bakerman2 - nice
@paperbo
This overwrites a sheet in other workbook with "Unique"'s values - is that what you want?
You could create the unique list directly in the other workbook (instead of "Unique")Please Login or Register to view this content.
- it would be fairly easy to amend the code
- let me know
- I think this should work with Excel 2003
- Barcode count values placed in a new worksheet named "Unique"
- making it easy to delete that worksheet if you then copy them elsewhere
- run in attached workbook with {CTRL} k
How it works:
- sheet containing barcodes activated to set the correct range of values to use
- new worksheet added
- barcode values attributed to new sheet (more efficient than copy/paste) starting at A2
- new values sorted
- TWO counts calculated for each barcode (total and cumulative to each row)
- total count placed in columnB (BUT only against first occurence of barcode - ie when cumulative count = 1)
- rows where cell in columnB is BLANK deleted (identified using SpecialCells property)
Any questions?
- you must ask if there is anything in the code that you do not fully understand
- if you need further help in taking things to the next stage let me know
Attachment 555771Please Login or Register to view this content.
Hi again,
There is always something that gets thrown into the mix at some point ! I am currently messing about with the interface on the notebook to make it user friendly and it started throwing up an error. I traced it to if there are no duplicates then the macro fails. It is failing at this point.
It opens up a new workbook sheet called sheet1 then stops. If you start again and add a duplicate then all is well.Please Login or Register to view this content.
Try
SpecialCells fails if no cells foundPlease Login or Register to view this content.
Hi,
That works perfectly.
Many thanks.
Hi,
All is going well with this project and the advice so far has been excellent. I have uncovered anotther issue however. Part of the sheet contains a Macro that allows the deletion of the last stock itam. This originally worked fine but I have a problem now. See code below
For one form of stock this works fine as its numerical but if the stock code is prefixed by a letter then it does nothing as the formula contains an IfNumeric statement. I can only see 2 ways of solving this. I cant get IfText to work as the string contains 1 letter then 4 numbers and another letter. It might be possible to get the macro to ignore the first letter and use the second character number but not sure if this is the best solution.Please Login or Register to view this content.
What is the purpose of the 3rd line?
- suggest you delete it
(doing nothing because you are using variable "i" again on the next line)
What tests do you want the stock code to pass
- Is it always alphanumeric?
- Could it be either alpha or numeric only? etc
And perhaps more importantly....
- What types of values could be in the cell when you want it to fail the test?
Hi,
Sorry, the 3rd line of code shouldn't exist as I was experimenting.
The deletion of the original numbers (1001,1002,1003 etc...) works fine for that specific worksheet. On another worksheet I am using the same formula but I have codes (R1002, R1023, R1027a etc...). I need to apply a Macro that will recognise the values. Its irrelevant what the values are (mix of letters and numbers). It just needs to acknowledge that there is a value and remove it. It is not conditional as removal is decided by the user. The 1st character is always a letter and the second is always a number.Please Login or Register to view this content.
Last edited by paperbo; 01-16-2018 at 01:22 PM.
I have already replied to this on post#20.
I have edited post #21. not sure how I resent it ??
If it needs a value of any kind then try
Please Login or Register to view this content.
Had to tweak it a bit but its working fine now and deleting the contents of a cell, irrespective of character type, with a form button. That is now the end of this and i would like to thank you again for your amazing advice.
Glad you are sorted
You can mark the thread as SOLVED by clicking on ThreadTools at top of thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks