Solved: Matching by columns entered

[johnske]

Hi all,

This is the start of a project that's still in the initial design stage and I would like some ideas for the best way to go about this first bit....

What I have is 2 worksheets, each with the same number of columns (52) and both sheets have identical headings. On each row there will be either 1, 2, 3, 4, or 5 cells that have entries.

Sheet1 is a "definitions" or "reference" sheet that defines the name attached to any unique combination of entries. E.G. if there are entries in B6, C6, D6, E6 the name in A6 will be defined as "Type1", if there are entries in B7, D7, E7 the name in A7 will be defined as "Type2", and so on...

Sheet2 is an "data-entry" sheet where 1, 2, 3, 4, or 5 entries - numbers - will be made on each row.

What I need now is, when any entries are entered on Sheet2 (there will be an "Enter" button)...Sheet1 will be searched row by row to find a matching combination for the data-entries in the columns in Sheet2 and the name of this combination (Sheet1, A column) is then copied to the A column in Sheet2 next to the new entry....If there is no match, we want an error message.

In addition, we want to conditionally format the new entry so that, if any one of the 1 to 5 entries is larger than any previous entry in the same column, it will be highlighted.

A copy of the initial layout is attached, any ideas would be appreciated (note that it's a VBA solution that's needed, not a spreadsheet solution)

John :bink:

[CBrine]

John,

Why not just use a vlookup formula. Just add a hidden concontanated column listing B2&C2&D2&E2&F2 to your Type sheet prior to the type column, and the following vlookup to your data entry sheet


=if(iserror(Vlookup(B2&C2&D2&E2&F2,YourRange,2,false)),"Error",Vlookup(B2&C2&D2&E2&F2,YourRange,2,false))

YourRange = The range of your TYPE data.


HTH
Cal

No code or button required.

[johnske]

John,

Why not just use a vlookup formula. Just add a hidden concontanated column listing B2&C2&D2&E2&F2 to your Type sheet prior to the type column, and the following vlookup to your data entry sheet

=if(iserror(Vlookup(B2&C2&D2&E2&F2,YourRange,2,false)),"Error",Vlookup(B2&C2&D2&E2&F2,YourRange,2,false))

YourRange = The range of your TYPE data.


HTH
Cal

No code or button required.No, sorry Cal, not practical, want to get right away from spredsheet solutions ...This is only one very small part of a large project, it's only one data sheet in one workbook and there may be any number of them (up to several hundred workbooks - created as needed), there will actually be only one reference sheet in a 'Master' book that's referenced by all the others.

I have done this as spread-sheet coding in a previous version and the problem was that it got quite out of hand memory-wise when there were only 50 books (with help files etc it was something like 200MB)...and that's not the end of the story...all the highlighted cells need to be compared against those in other books and the highest of these put in a separate book as 'records' :bink:

[johnske]

PS We don't know how many entries (rows) there will be used in any book, there may only be one or two, OR, there may be many hundreds, so we want coding to be at an absolute minimum.

I'm looking the idea of using at a single master book (that has all the VBA coding in it) that opens the individual books, formats the rows one at a time, and makes entries in them - also, there are actually TWO of these data sheets in each book, the other has many more columns than the one in the zipped example :bink:

[CBrine]

John,
What about a change event driven search. The reason I ask this is that I can build you the code required to do the search, based on the activecell.row, but if you are using a button, what's to stop the user from moving to a different row prior to pressing the button. You will then end up with the search looking for the wrong row info?
If you drive it off of the sheet_change event based on a change within the current row, you will end up with extra processing occurring each time an entry is made? So, I'm not sure what the best solution would be? Do all the columns need to have an entry prior to the search(I don't think that's the case though).

Any thoughts?

[johnske]

OK, In the master book we have to have an abridged, more user-friendly method to make all the preliminary entries (I think anyone would agree that 52+ columns to search through to make 3 or 4 entries in each row is just too much and thus prone to error).

These entries then need to be checked (by putting them in another form and showing the name) before actually writing them in the target book...If checking shows they're correct, an "Enter" button then searches for an empty row on the target sheet, formats the row, enters the values, and does a "sort-by-date" (as these may not be actually entered until some later date), and then highlights any cells that have a value larger than a previous entry.

Also, in addition to this, we need to hide all columns that have no entries in them (to save ink when a printout is required) - Note that not all columns WILL be used, as the data-sheet is a "general-purpose" type that is intended to cover all ages etc., so for any one person quite a few columns will be hidden...

(If we have a change driven event, there will be unwanted code in all the books - driving up the memory usage) :bink:

[johnske]

PS Here's a copy of an idea (uncoded) I've been toying with for the input sheet in the master book........

The other idea, and this may be simpler, is a multi col list box that simply accesses a summarized version of what's on the reference sheet and you then click the relevant name/distances etc in the list box to obtain a userform with the name and distances displayed on it and four textboxes to enter the score - (now that I've put it in writing, this actually seems very much simpler and may be a better option) - it's 3am here - bedtime :bink:

[CBrine]

John,
OK, I think I have a better handle on what you are looking at. Couple of questions. Your master sheet, is your entry sheet, but you don't want change driven code on this sheet, and also not a vlookup solution. Since you don't want them to push the update button until after they have determined that the entry is valid, we need to have something to trigger the validation code? Do you want 2 buttons, one to validate and one to update the Data Workbook?
Just to confirm, once you do have a valid entry and the update is pressed, you want the entered record to be copied to your Target workbook in the last open row, then have some conditional formatting added, and have it sorted by date.



Edit: Or I could have it backwards? When you do want the data validated? When it's entered in the inputsheet, or when it's copied to the master sheet?

[johnske]

Validation is visual, they may have a scoresheet with (say) 11/11/04, Mens FITA, (4 scores) 275, 300, 300, 320, and Total=1195.

However the wrong name for the round (thru ignorance) may've been written down - but they cant mistake the distances, so we enter the score for each distances as the main priority and this then checks that the right name was written down and that the total is correct.

(Conditional formatting goes on the target sheet)

It's late, I'm tired - tomorrow :snooze

[johnske]

Hi Cal,

Had a bit of a sleep and a think. I see what you're getting at now...

As all the code is to be in the "master" book, we CAN use a change event there. (as we're not skimping on code in the "Master" book)
i.e. we can have a hidden single-row "Temporary" sheet that the input sheet initially writes to, the changes to this temporary sheet then yields the 'Round Name' from the definitions sheet.

The 'Round Name' on the input sheet can simply be linked to the name on this temporary sheet and, if correct, the user then clicks the input button to copy this from the temporary sheet to the next empty row in the other (uncoded) book and perform all the other actions such as sorting etc, the entry on the temporary sheet can then be cleared ready for the next entry.

(Just writing all this down and discussing it is making me think more logically about all this - it doesn't seem all that difficult now)

John

[CBrine]

Your sample input workbook has update buttons on it, but they will also need a verify button. Here's what I think needs to happen, just a general outline. Correct me where I'm wrong.

PS-I think we will only need 2 buttons, at the top, and we can freeze the panes at the top with the 2 buttons. We can then use activecell.row to get the correct data.

Verify Button
1. User makes entry.
2. User presses the verify button.
2. Code is executed to disable events.(to stop enable/disable macro's prompt)
3. Master sheet is opened.
4. enable events
5. Cycle through rows on master sheet listing, looking for match.
6. Return match if found, or error to Input sheet if not found.(column A)


Update Button.
1. User presses update button.
2. Check column A to confirm that no error occurred on validation.
3. Disable events
4. Open Master Sheet
5. Enable events.
6. Find last open row in data sheet of Master workbook.
7. Update data.
8. Sort data based on date.
9. Close Workbook with save
10. We should add an updated flag to the input sheet that we can key off of also to prevent double updates, unless you want them to be able to clear and reuse.

Let me know your thoughts. We can most likely store most of the code we need in macro's on the master sheet, although the opening of the master sheet code will need to exist on the input sheet.

Cal

[johnske]

Hi Cal,

This is what I've come up with so far - In this attachment I've made links between 2 "Temp" sheets and the input sheet (still needs to be checked for errors though). I still need to put in an inbuilt check so that when the input button is clicked on the active row, entries on all other input rows on the InputSheet are deleted (to prevent data errors).

The "Temp" sheets are linked to the InputSheet so that all entries on the InputSheet are shown on the "Temp" sheets, while the "Round" cell on the InputSheet is linked to the "Round" cell on the Temp sheet.

All that is really needed now is the worksheet change event on the temp sheets so that a search of the definitions sheet recovers the name of the round for the temp sheet, and, in turn, this can then be read on the input sheet. (note: If the search fails, I want to give the user the option of "check your entries - unlisted round" (and) "do you want to list this as a new round?" - {Give it a name and list it}).

As to another question of yours, the problem of editing incorrect entries in the uncoded "Slave" books arises. This can be resolved by deliberately making a second entry for the same date as the incorrect entry, a quick search thru the dates in column A in the slave book can then prompt a message box asking "Overwrite?"/"Delete previous?", or, "Additional same-date entry?"

John

EDIT: Having problems, attachment wont upload, will try again...

[johnske]

Still cant upload...I know there were problems with the server today (I still haven't received your last post as an email either) so it seems the problems are still there. Will try again tomorrow...
John

[johnske]

Cal,here it is...

(n.b. The "Search failure" error message only needs to be launched when the user tries to enter the values for the scores despite the fact that the "Round" cell on the input sheet is empty)

[johnske]

Cal,

This is probably better...Found an error in the links to the "Notes" cells and had to replace the previous links with a nested IF statement.

Also added a WIP "MAIN" page with notes outlining what the over-all end result of the whole thing will be...

John

[CBrine]

John,

My thoughts on the validation are this. I'm not sure why you want to use the temp sheet, but I ran some quick tests and a formula linked cell does not trigger a change event, unless the actual formula is changed. I again think you will need to trigger the validation with a button. Here's some code I put together to valididate the data for a AA Round.


Sub CheckAA()
dim SearchString as string, Cell as range, is as worksheet, ds as worksheet, FoundString as string
set is = Sheets("InputSheet")
set ds = Sheets("DB_30")
is.activate
SearchString = trim(range("B5") & range("C5") & range("D5") & Range("E5") & Range("F5") & range("G5") & range("H5") & Range("I5") & range("J5") & range("K5") & Range("L5") & range("M5"))
ds.activate
for each cell in ds.range("C6:M31")
FoundString = cell & cell.offset(0,1) & cell.offset(0,2) & cell.offset(0,3) & cell.offset(0,4) & cell.offset(0,5) & cell.offset(0,6) & cell.offset(0,7) U cell.offset(0,8) & cell.offset(0,9) & cell.offset(0,10)
if FoundString = SearchString then IS.range("N35") = cell.offset(0,-1)
Next
is.range("N35").value = "#ERROR#-Not Found"
End sub

[johnske]

Hi Cal,

Hey, you're right! I didn't think of it...Worksheet Change doesn't work with the linked values. However, I tried Worksheet Calculate (which is similar) and found it (Calculate) does work... :)

As to an extra button...Have to keep in mind the end-user, there will be a lot of buttons to click anyway, and if there are too many, this becomes very annoying (and energy-consuming) for them. So, IF at all possible - avoid any more than is absolutely neccessary (that's why I went for the linked s/s solution).

Was the code you gave intended for one very specific round or for all rounds on one definition sheet? - I could only get #ERROR#-Not Found whenever I ran it... :confused:

In the meantime, I needed some code to validate any new rounds the user may add in the DB_30 and 36 sheets. (I.E. This code tells whether any particular round already exists - whether it's known by a different name or not)

This is done by assigning a unique binary number to each round (row), and this number is dependent on which columns there are entries in. We then check if there are any two rows that have the same number assigned to it, and if there are, there is a duplicate... (see my post, Array Question - look for duplicates)

Then I thought - this idea can be easily modified and extended as a quick & simple way to find the name of the round from the entries that are made on the Temp sheet (as the temp sheet is laid out in exactly the same way as the DB_30 and 36 sheets {whereas the input sheet is not}).

I.E. assign this number as an ID number on the DB_30 (or 36) sheet in the empty A column, search this sheets A column until a row is found with an ID number that matches the number obtained similarly from the temp sheet and - Voila! The name is in the B column on that row. E.G.



Option Explicit
'code for sheets (DB_30 & 36) where all rounds are defined

Private Sub CheckIsNew36RoundButton_Click()
Dim Found, i%, N%, BinaryNum#, BinarySum#, IdNumber#
Application.ScreenUpdating = False
For N = 6 To 56 'rows 6 to 56
With Worksheets("DB_36").Rows(N)
Set Found = .Find(what:="*", LookIn:=xlValues, searchorder:=xlByRows)
If Not Found Is Nothing Then
BinarySum = 0
For i = 1 To 7
Set Found = .FindNext(Found)
BinaryNum = 2 ^ (Found.Column)
BinarySum = BinarySum + BinaryNum
Next i
'//put unique ID numbers in column A
Range("A" & N) = BinarySum
End If
End With
Next N
Application.ScreenUpdating = True
Range("A65536").End(xlUp).Select
IdNumber = Range("A65536").End(xlUp).Value
With Range("A6:" & Selection.Offset(-1, 0).Address)
Set Found = .Find(what:=IdNumber, LookIn:=xlValues)
If Not Found Is Nothing Then
'//there is a duplicate
MsgBox "Sorry, your entry for a " & ActiveCell.Offset(0, 1) & _
" round" & vbLf & _
"duplicates a pre-existing round and will be deleted", _
, "ERROR ! - Duplicated entry."
ActiveCell.EntireRow.ClearContents
ActiveCell.Offset(0, 1).Select
Else
'//there is no duplicate
ActiveCell.Offset(1, 1).Select
MsgBox "Congratulations, your " & ActiveCell.Offset(-1, 0) _
& " round is indeed a new round", , "This New Round Has Been Listed..."
End If
End With
End Sub

Option Explicit
'code for the temp sheets

Private Sub Worksheet_Calculate()
Dim FindIt, Found, i%, BinarySum#, BinaryNum#
Application.ScreenUpdating = False
If Range("30Temp!A6") = Empty And Range("30Temp!AD6") <> 0 Then
MsgBox "Entering the date will stop this really" & vbLf & _
"annoying message from popping up :o)", , "WHAT DATE??..."
End If
With Worksheets("30Temp").Rows(6)
Set Found = .Find(what:="*", LookIn:=xlValues, searchorder:=xlByRows)
If Not Found Is Nothing Then
BinarySum = 0
For i = 1 To 7
Set Found = .FindNext(Found)
BinaryNum = 2 ^ (Found.Column)
BinarySum = BinarySum + BinaryNum
'(this number matches the number for the Geelong
'round on sheet DB_30, needs to be checked more)
Next i
End If
End With
'//the following is not working yet - (ideas needing to be worked on)
With Worksheets("DB_30").Range("A6:A56")
Set FindIt = .Find(what:=BinarySum, LookIn:=xlValues, searchorder:=xlByColumns)
If Not FindIt Is Nothing Then
FindIt.Address.Select
Range("30Temp!B6") = Selection.Offset(0, 1)
End If
Application.ScreenUpdating = True
End With
'//do some other things here
End Sub

John

[johnske]

Hi Cal,

I got the unworking part of the previous code working with some small mods (as below)

This give the required result(s) - (try attachment), so (unless you've come up with something better in the meantime) I guess I only have to thank you for your feedback and input and can mark this as solved?..

Regards,
John


Option Explicit

Private Sub Worksheet_Calculate()
Dim FindIt, Found, i%, BinarySum#, BinaryNum#
Application.ScreenUpdating = False
If Range("30Temp!A6") = Empty And Range("30Temp!S6") <> 0 Then
MsgBox "Entering the date will stop this really" & vbLf & _
"annoying message from popping up :o)", , "WHAT DATE??..."
End If
With Worksheets("30Temp").Rows(6)
Set Found = .Find(what:="*", LookIn:=xlValues, searchorder:=xlByRows)
If Not Found Is Nothing Then
BinarySum = 0
For i = 1 To 7
Range("B6") = "Unlisted"
Set Found = .FindNext(Found)
BinaryNum = 2 ^ (Found.Column)
BinarySum = BinarySum + BinaryNum
Next i
End If
End With
Worksheets("DB_30").Select
With Worksheets("DB_30").Range("A6:A56")
'//look for the same number in column A on the definitions sheet
Set FindIt = .Find(what:=BinarySum, LookIn:=xlValues, searchorder:=xlByColumns)
If Not FindIt Is Nothing Then
FindIt.Select
'//put the name of the round on the temp sheet
Range("30Temp!B6") = Selection.Offset(0, 1)
End If
End With
Worksheets("InputSheet").Select
Application.ScreenUpdating = True
End Sub