Solved: Cut and Paste using Offset and Vlookup

[smugdruggler]

Hi Guys,

I have a Workbook that details Customers and the Newspapers they receive for each day of the week. The Customers Sheet has a Matrix of Name, Address, Monday, Tuesday etc across the columns, and for each Row The Name, and a paper code for each day of the week ie. Tel for Telegraph etc.

On the Data Sheet I have a lookup Table detailing paper code against paper.

What I am trying to do is this:-

For each Day say Monday, create a list of Names and the Papers that they receive and paste this onto a Sheet called Monday.

I have managed to crib together the following code, and have suceeded in finding what papers are required, but can only paste the codes I find onto the Monday Sheet, not the corresponding names.

I would appreciate any help.


SD




Option Explicit

Private Sub cmdMonday_Click()
Dim ws1 As Worksheet
Set ws1 = Sheets("Customers")
ws1.Activate
'Call the macro with Monday range.
'This will change for each Day
With ws1
Call SelectPaper(.Range(.Cells(5, 5), .Cells(.Rows.Count, 5).End(xlUp)))
End With
End Sub


'Called from the cmdMonday_Click() Routine on the 'Orders' Sheet
Sub SelectPaper(rngSearch As Range)
Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim rngMonday As Range, Cell As Object

Application.EnableEvents = False
On Error GoTo ErrUm

Set wb = ActiveWorkbook
Set ws1 = Sheets("Customers")
Set ws2 = Sheets("Monday")
Set ws3 = Sheets("Data")

'Check every cell in the range to see who gets a paper.
For Each Cell In rngSearch
If Cell.Value <> "\" Then
If rngMonday Is Nothing Then
Set rngMonday = Range(Cell.Address)
Else
Set rngMonday = Union(rngMonday, Range(Cell.Address))
End If
End If
Next
'Select the new range of only those who get a paper
rngMonday.Select
'Copy Selection to Monday Sheet
Selection.Copy ws2.Range("B1")
ws2.Activate

Application.EnableEvents = True
Exit Sub

ErrUm:
MsgBox "Doh! Another Error!" & vbNewLine & Err.Description
Err.Clear
Application.EnableEvents = True

End Sub

[Bob Phillips]

Can you post your workbook, it is tricky envisaging the data.

[smugdruggler]

No Problem!

Yer Tiz.


SD

[Bob Phillips]

Private Sub cmdMonday_Click()
Dim aryDays
Dim i As Long
Dim rng As Range
Dim iCol As Long

Application.EnableEvents = False
On Error GoTo ErrUm

aryDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
Dim ws1 As Worksheet
Set ws1 = Sheets("Customers")
For i = LBound(aryDays) To UBound(aryDays)
iCol = i - LBound(aryDays) + 5
With ws1
Set rng = .Range(.Cells(5, iCol), .Cells(.Rows.Count, iCol).End(xlUp))
End With
Call SelectPaper(rng, Worksheets(aryDays(i)))
Next i

Exit Sub

ErrUm:
MsgBox "Doh! Another Error!" & vbNewLine & Err.Description
Err.Clear
Application.EnableEvents = True
End Sub
'Called from the cmdMonday_Click() Routine on the 'Orders' Sheet
Sub SelectPaper(rngSearch As Range, sh As Worksheet)
Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim rng As Range, Cell As Range
Set wb = ActiveWorkbook
Set ws1 = Sheets("Customers")
Set ws3 = Sheets("Data")

'Check every cell in the range to see who gets a paper.
For Each Cell In rngSearch
If Cell.Value <> "\" Then
If rng Is Nothing Then
Set rng = Cell
Else
Set rng = Union(rng, Cell)
End If
End If
Next
'Select the new range of only those who get a paper
rng.Copy sh.Range("B1")
Application.EnableEvents = True
Exit Sub

End Sub

[Simon Lloyd]

Can you attach your workbook?, this can probably be solved with a worksheet function copied and pasted in to the sheets named days of the week so when ever the master sheet is changed then so is the week day sheet. You could of course use an Indirect VlookUp for the name of the person if they appear for say a Monday....or something along those lines!

Regards,
Simon

[smugdruggler]

Hi Simon,

See earlier post for Workbook.

SD

[smugdruggler]

Hi XLD,

Copied Code to a new Module and ran it, but I get a subscript out of range error. The Monday paper codes still get copied to the Monday Sheet, but no Names or Papers from the Data Sheet.

SD

[Bob Phillips]

Nor did your code do that, I just utilised your code and extended it for more sheets.

Ti get past the subscript error, you have to add tabs for Tuesday, Wednesday etc.

[Simon Lloyd]

I Can't believe it...........i've been dissapointed by the Great "El Xid"!, i was kinda hoping that the solution would have been a single sheet with formula in, perhaps a dropdown in A1 to select the day and on selecting the day the relevant data appearing at the formula cells, i have been fiddling around with limited sucess with Match and Offset, as
=HLOOKUP(A1,INDIRECT(C1),5,FALSE)this kind of formula didnt seem to work too well....i am trying to concoct something and still will.

Still trying to be imaginative but limited ability!

Regards,
Simon

[smugdruggler]

Thanks Guys,

Really appreciate it.

SD

[Bob Phillips]

Simon,

I think it is a bit mor complex than that.

The list on Monday would be constructed with this array formula

=IF(ISERROR(SMALL(IF((Customers!$E$5:$E$50<>"\")*(Customers!$E$5:$E$50<>""),ROW($E$5:$E$50)-MIN(ROW($E$5:$E$50))+1,""),ROW($A1))),"",
INDEX(Customers!$E$5:$E$50,SMALL(IF((Customers!$E$5:$E$50<>"\")*(Customers!$E$5:$E$50<>""),ROW($E$5:$E$50)-MIN(ROW($E$5:$E$50))+1,""),ROW($A1))))
and the name is

=IF(B1="","",VLOOKUP(B1,Data!B4:D58,2,FALSE))

[Bob Phillips]

Simon,

I think it is a bit mor complex than that.

The list on Monday would be constructed with this array formula

=IF(ISERROR(SMALL(IF((Customers!$E$5:$E$50<>"\")*(Customers!$E$5:$E$50<>""),ROW($E$5:$E$50)-MIN(ROW($E$5:$E$50))+1,""),ROW($A1))),"",
INDEX(Customers!$E$5:$E$50,SMALL(IF((Customers!$E$5:$E$50<>"\")*(Customers!$E$5:$E$50<>""),ROW($E$5:$E$50)-MIN(ROW($E$5:$E$50))+1,""),ROW($A1))))
and the name is

=IF(B1="","",VLOOKUP(B1,Data!B4:D58,2,FALSE))

[Simon Lloyd]

I was fiddling with a multiple result array formula
=IF(ROWS(A1:A1)<=COUNTIF(Customers!A2:N2,$A$1),INDEX(Customers!E2:N2,SMALL(IF(Customers!A2: N2=$A$1,ROW(Customers!A2:N2)-ROW(A2)+1),ROW(Customers!A1))),"")however this only produces the results for the text typed in A1 so if i typed 6 then (as long as i had copied the formula down) this would produce 3 lots of Zebedee, i am trying to adapt it but hitting brick walls!

Thanks Bob made me smile when you replied!, wasn't being horrible just following some advice a friend gave me in a PM once!

Regards,
Simon

[Bob Phillips]

Thanks Guys,

Really appreciate it.

SD

Does that mean you are sorted?

[Bob Phillips]

I have been thinking again about Simon's musings, and as usual he is right.

Attached is a workbook, where you can select the day from the dropdown in C1, and the Daily sheet will reflect that day's papers.

BTW, I see the Salisbury Journal in your list. Are you in that area? That is just up the road from me, I am in Poole.

[Bob Phillips]

Oops, didn't make a formula absolute.

[smugdruggler]

Yeah I'm just north of Shaftesbury small world!

That is really clever by the way, thanks a huge amount El Xid, would not have got there without your help.

SD

[Simon Lloyd]

Not alway right Bob - Just sometimes imaginative!!!! Lol....i knew that the index formula wasn't excatly the way to go but thought match was but couldn't get close enough with a solution myself to try and make it work in the application _ My faith is now restored!!

Regards,
Simon

[Simon Lloyd]

Bob, looking at the formulae you devised i can see elements of what i was trying to do with the array formula - naturally yours is far beyond me so a little explanation if you would?



Customers!$E$3:$K$3,0)-1,46,1)<>"\")*(OFFSET(Customers!$E$5,0
why the "*"?, i couldn't see the relationship in multiplying the ranges or is it used as a wild card? similarly with this next excerpt the "-MIN"


ROW(Customers!$E$5:$E$50)-MIN(ROW(Customers!$E$5:$E$50))+1,""),
with the rest of the formula i can work out what its looking at where and why but i wasnt entirely sure with this combination
IF(ISERROR(SMALL(IF((OFFSET, however your formulae work very neatly!

Regards,
Simon

[Bob Phillips]

Bob, looking at the formulae you devised i can see elements of what i was trying to do with the array formula - naturally yours is far beyond me so a little explanation if you would?

why the "*"?, i couldn't see the relationship in multiplying the ranges or is it used as a wild card?

That is effectively an AND Simon. I want to make two tests, that the cell is not a \ and it is not blank. If both conditions are met it will give me a 1, if either is not met, it will give me a 0. As you know, any non-zero value in Excel is a TRUE, so the true action of the IF formula is taken when it equals 1.


similarly with this next excerpt the "-MIN"

The MIN is just to handle the fact that the data might start in row 1, row 2, or row 5, etc. ROW(E5:E50) gives me an array of 5,.6,7,..., MIN(ROW(E5:E50)) gives me 5 every time, ROW(E5:E50)-ROW(ROW(E5:E50))+1 gives me an array of 1,2,3,..., which I can pass to SMALL to get the values in order. I could have just used ROW(A1:A46), but that is neither intuitive (what does A1:A46 have to do with E5:E50?), and would fail if a row were inserted before 1. Totally dynamic.


with the rest of the formula i can work out what its looking at where and why but i wasnt entirely sure with this combination , however your formulae work very neatly!

The OFFSET is used because I need to test different columns based upon the day, so I offset E5 based upon the day number -1 (Monday is OFFSET 0), and use the number of rows to size the array to test. I was a bit lazy here, I should have used INDEX, but OFFSET is easier, so I didn't spend the time on INDEX.

In essence, all my formula does is to test build an array of row indexes for each row that meets the test criteria (not \ and not blank) for the chosen day, these are then passed one at a time to the INDEX function to return the customer and paper id.


I think your formulae were trying to follow the same principles, you just don't have the skills (YET!) to follow it through.

Bob

[Simon Lloyd]

Thanks for taking the time to explain so well Bob, i did understand that using MIN gives you the MINIMUM row number to start at but when i saw -MIN, being unskilled it looked like a substraction just like * looked like a multiplication.

Again thanks........and as ever i will keep trying!

Regards,
Simon

[Bob Phillips]

Well it is a subtraction, although the neat thing here is that it subtracts that value from EVERY element of the previous array.

Honestly, if you could find some time to come down here, a couple of days talking it through and working some examples, you would understand so much more quickly. Sharing this info this way is difficult, but at least you are trying in other forums (BTW< I see you area moderator at one of them, forget which. Keen aren't you, but good for you :)).

[Simon Lloyd]

Bob, thanks, will definately try to get to you during this year (sounds like a long time, but i don't need to tell you how it flies when you have so much to do!).......as for other thread reply.....Fanciful??, i think i'm in the bungalow stage skyscrapers are a long way off!

Regards,
Simon