Something like vLookup for duplicate values on other tab

[Caveman1957]

Hi,
I need to do something like vLookup which will populate calls on one ta
with information from another tab where the criteria will be matched i
more than one row on the other tab without having blank lines on th
destination tab.
Can anyone help or am I being too ambitious

 

[Caveman1957]

Hi,

Not too ambitious at all. Perhaps you could post a dummy workbook wit
some examples of what you need as results included?

File Attached eventually

Main Data is in tab SS
In tab '7945 Users' I need to populate cells in green columns with dat
from SS tab if (in SS tab) ColumnC = 7945 and ColumnH = Yes

In tab '7945 NoVM' I need to populate cells in green columns with dat
from SS tab if (in SS tab) ColumnC = 7945 and ColumnH = No


Hope this is enough to get you started.

Thank

+-------------------------------------------------------------------
|Filename: BAT Preparation (sample).zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=579
+-------------------------------------------------------------------

 

[Caveman1957]

Are you adverse to having a few hidden columns to do all the hard wor
for you?

Basically, there are two ways of doing this that I can think of. On
involves some quite complex (and by that I mean awkward to maintain whe
data requirements change) formulas. The other involves some hidde
"helper columns" that are nice and easy to maintain.

Hi Spencer,
Hidden columns would be fine as long as they are to the right of th
existing data as I will need to copy paste blocks of the data to anothe
spreadsheet.

Data in the SS tab will change completely from job to job but th
columns will not. The other tabs will stay the same throughout if tha
helps any.

Thank

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[benmcclave]

Hi Caveman,

Try this code. You may need to change the sheet references and/or column references if I misinterpreted your sample. Otherwise, it seems to work fine on my machine.

Hope this helps,

Ben

Code:


Sub ParseData()
Dim lRow As Long
Dim sCol(1 To 5) As String
Dim lCol(1 To 5) As Long
Dim x As Long

'This sub will copy all pertinent data from the SS tab and copy it
'to two other tabs. Then, the sub will delete any unnecessary rows
'from these two tabs.

Application.ScreenUpdating = False 'Speeds up macro

'Last row of data on SS tab
lRow = Sheet8.Range("A50000").End(xlUp).Row

'Set the column references for the SS tab
sCol(1) = "F"
sCol(2) = "G"
sCol(3) = "E"
sCol(4) = "L"
sCol(5) = "N"

'Set the column reference number for the Users tab
lCol(1) = 1
lCol(2) = 2
lCol(3) = 4
lCol(4) = 5
lCol(5) = 6

'Copy data to tabs (Note: only copying the pertinent columns)
For x = 1 To 5
Sheet8.Range(sCol(x) & "2:" & sCol(x) & lRow).Copy
Sheet4.Range(Cells(2, lCol(x)).Address, Cells(lRow, lCol(x)).Address).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next x

'Delete unused rows
For x = lRow To 2 Step -1
If Sheet8.Range(Cells(x, 3).Address).Value = "7945" And _
Sheet8.Range(Cells(x, 8).Address).Value = "Yes" Then
'Do nothing
Else
Sheet4.Range(x & ":" & x).Delete (xlUp)
End If
Next x

'Repeat for NoVM tab
lCol(1) = 1
lCol(2) = 2
'lCol(3) = 4 'Not used
lCol(4) = 5
lCol(5) = 6

'Copy data to tabs (Note: only copying the pertinent columns)
For x = 1 To 5
Sheet8.Range(sCol(x) & "2:" & sCol(x) & lRow).Copy
If x <> 3 Then Sheet5.Range(Cells(2, lCol(x)).Address, Cells(lRow, lCol(x)).Address).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next x

'Delete unused rows
For x = lRow To 2 Step -1
If Sheet8.Range(Cells(x, 3).Address).Value = "7945" And _
Sheet8.Range(Cells(x, 8).Address).Value = "No" Then
'Do nothing
Else
Sheet5.Range(x & ":" & x).Delete (xlUp)
End If
Next x

Application.ScreenUpdating = True

End Sub

 

[benmcclave]

Good Morning,

I thought about it some more, and I think you could do it just as easily with formulas. First, add a formula to any available column on the SS sheet (I used column S in the example below, but you may use a different one). In row 2 of that column enter the formula and fill down:

=C2&H2&COUNTIFS($C$2:C2, C2, $H$2:H2, H2)

The result will be a string with the model number, Yes/No, and a running count. For example, "7945Yes1" or "7945Yes23".

Now, go to the tabs with your data and enter an INDEX formula to MATCH the items you want and pull in the correct column. In this example, I placed the formula in Cell B2 and referenced the SS tab range A:S, where column S contains the formula mentioned earlier in this post. The formula to enter is:

=IF(ISNA(MATCH("7945Yes" & ROW(A1), Sheet1!S:S,0)), "", INDEX(SS!A:S, MATCH("7945Yes" & ROW(A1), SS!S:S,0), 4))

In this formula, any "#N/A" values return a null string. Since the formulastarts in row 2, I use a relative reference to the row of cell A1 to increment the counter (the formula ROW(A1) returns "1", so as you copy it down, the number increases by one each row).

If there is a match for the model number + "Yes" + the counter, then the formula will index against columns A:S on the SS tab and use the MATCH function to pull in the relevant row. Finally, the "4" at the end is telling theIndex function what column to pull in. In this case, I wanted column D, which is the 4th column in the range A:S.

Take care,
Ben

 

[Caveman1957]

Good Morning,

I thought about it some more, and I think you could do it just as easil
with formulas. First, add a formula to any available column on the S
sheet (I used column S in the example below, but you may use a differen
one). In row 2 of that column enter the formula and fill down:

=C2&H2&COUNTIFS($C$2:C2, C2, $H$2:H2, H2)

The result will be a string with the model number, Yes/No, and a runnin
count. For example, "7945Yes1" or "7945Yes23".

Now, go to the tabs with your data and enter an INDEX formula to MATC
the items you want and pull in the correct column. In this example,
placed the formula in Cell B2 and referenced the SS tab range A:S, wher
column S contains the formula mentioned earlier in this post. Th
formula to enter is:

=IF(ISNA(MATCH("7945Yes" & ROW(A1), Sheet1!S:S,0)), "", INDEX(SS!A:S
MATCH("7945Yes" & ROW(A1), SS!S:S,0), 4))

In this formula, any "#N/A" values return a null string. Since th
formula starts in row 2, I use a relative reference to the row of cel
A1 to increment the counter (the formula ROW(A1) returns "1", so as yo
copy it down, the number increases by one each row).

If there is a match for the model number + "Yes" + the counter, then th
formula will index against columns A:S on the SS tab and use the MATC
function to pull in the relevant row. Finally, the "4" at the end i
telling the Index function what column to pull in. In this case,
wanted column D, which is the 4th column in the range A:S.

Take care,
Ben

Thanks for both ideas Ben.

I had the most success with the code.

The first formula for the index worked fine, but I had issues with th
second one to go in the target tab. It kept trying to open new workbook
and the data did not get pulled across.

I will try and split up your code so I can have a set of tab specifi
subs to do the data transfers. I also want to try and get the dat
without the blank lines, more on the lines of my Pseudo code, as I ende
up with rows with no formulae in columns G through M in the noVM tab.

This might take more work at the front end but I should not need t
change it much once it is working.

Notice what an optimist I am

I will let you know how I get on

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Caveman1957]

Thanks again Ben for your inspiration to kick me off


I have added the following code to the TAB code page and it transfer
the correct data with no blank lines.

All I need now is an easy way to manually trigger the sub without goin
in to the developer ribbon.

Is it possible to add something to the Data ribbon which will run thi
sub name on the current tab?

Sub ParseData()
Dim lRow As Long
Dim lsCol(1 To 5) As Long
Dim ldCol(1 To 5) As Long
Dim x As Long
Dim sr As Long
Dim dr As Long

'This sub will copy all pertinent data from the SS tab and copy it t
current tab hopefully



Application.ScreenUpdating = False 'Speeds up macro

'Last row of data on SS tab Sheet8
lRow = Sheet8.Range("A3000").End(xlUp).Row

'Set the column references for the SS tab
lsCol(1) = 6
lsCol(2) = 7
lsCol(3) = 5
lsCol(4) = 13
lsCol(5) = 14

'Set the column reference number for the Users tab
ldCol(1) = 1
ldCol(2) = 2
ldCol(3) = 4
ldCol(4) = 5
ldCol(5) = 6

'Copy data to tabs (Note: only copying the pertinent columns)
'Sheet8 is SS and Sheet4 is 7945 Users
dr = 2
For sr = 2 To 3000
If Sheet8.Cells(sr, 3).Value = 7945 And _
Sheet8.Cells(sr, 8).Value = "Yes" Then
For x = 1 To 5
Sheet8.Cells(sr, lsCol(x)).Copy
Sheet4.Cells(dr, ldCol(x)).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next x
dr = dr + 1
Else
'Do Nothing
End If
Next sr



Application.ScreenUpdating = True

End Su

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[benmcclave]

I'm happy to help and glad to hear that things seem to be heading the right direction. Best of luck.

Ben

 

[benmcclave]

You might try changing the sheet name in the sub to "ActiveSheet" (i.e., find and replace "Sheet4" with "ActiveSheet"). Then, you could assign a keyboard shortcut to the macro. By changing the destination sheet to the active sheet, then the sub will run on whatever sheet was active when the keyboard shortcut was used.

If you use this method, you may want to add some form of validation at the beginning of the sub so that it doesn't inadvertently run on the wrong tab.This could be handled with one line:

If ActiveSheet.CodeName <> "Sheet4" And ActiveSheet.CodeName <> "Sheet5" Then Exit Sub

Another option might be to add a button to each sheet and tie the macro to that button. If you really need an option on the ribbon, I can't be of much help. But there is an article on the subject at http://msdn.microsoft.com/en-us/library/office/ee767705.aspx.

Ben

 

[Living the Dream]

Hi

You could try triggering it when you open the workbook.

Private Sub Workbook_Open()

Call ParseData

End Sub

HTH
Mick.

 

[Caveman1957]

You might try changing the sheet name in the sub to "ActiveSheet" (i.e.
find and replace "Sheet4" with "ActiveSheet"). Then, you could assign
keyboard shortcut to the macro. By changing the destination sheet t
the active sheet, then the sub will run on whatever sheet was activ
when the keyboard shortcut was used.

If you use this method, you may want to add some form of validation a
the beginning of the sub so that it doesn't inadvertently run on th
wrong tab. This could be handled with one line:

If ActiveSheet.CodeName <> "Sheet4" And ActiveSheet.CodeName <> "Sheet5
Then Exit Sub

Another option might be to add a button to each sheet and tie the macr
to that button. If you really need an option on the ribbon, I can't b
of much help. But there is an article on the subject a
http://msdn.microsoft.com/en-us/library/office/ee767705.aspx.

Ben

Thanks again Ben.

The issue I have is that each sheet has a sub ParseData() and th
criteria for the data and the cells copied can be different for eac
sheet.
I did think about the button route as I have used that before to sor
result league tables after they have been updated.

i will look into that link you provided to see if I can get th
equivalent of the button but not embedded on the sheet. I do not think
will have much success though after thinking about it. Still worth
try

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Caveman1957]

Thanks Mick.

I currently have a copy of the ParseData code under Private Su
Worksheet_Activate() with a check so it will only run if there is n
data in the sheet.
What I need now is a manual update option.
I did think about adding a dialog at the start of th
Worksheet_Activate() to ask if an update is required and then remove th
data exists check but I do not know how to do this.

I managed to get a dialog box designed with text and two option button
but how to get this to display and then receive the information on whic
button is pressed is beyond me. I was thinking that one button woul
return true and the other false.

Thanks to all who helped my brain get firing

Here is my current solution, which also has the manual update thin
fixed.

Code on the tab:

Private Sub Worksheet_Activate()

Dim Query As String


'This sub will copy all pertinent data from the SS tab and copy it t
current tab hopefully

If ActiveSheet.Cells(2, 1) = "" Then

ParseData
Else
'Ask whether to Update
Query = InputBox("Update Data? Y/N", "User Input")
If Left(Query, 1) = "Y" Or Left(Query, 1) = "y" Then
ParseData
Else
'Do Nothing
End If

End If

Application.ScreenUpdating = True

End Sub


Private Sub ParseData()
Dim lRow As Long
Dim sr As Long
Dim dr As Long

'This sub will copy all pertinent data from the SS tab and copy it t
current tab



Application.ScreenUpdating = False 'Speeds up macro

'Last row of data on SS tab Sheet8
lRow = Sheet8.Range("A3000").End(xlUp).Row

'Copy data to tabs (Note: only copying the pertinent columns)
'Sheet8 is SS
dr = 2
sr = 2
Do
If Sheet8.Cells(sr, 3).Value = CInt(Left(ActiveSheet.Name, 4)) And _
Sheet8.Cells(sr, 8).Value = "Yes" Then 'Check for VM user
Call PhoneDataCopy(sr, dr)
dr = dr + 1
sr = sr + 1
Else
sr = sr + 1
End If

Loop Until Sheet8.Cells(sr, 1) = "z"


Application.ScreenUpdating = True

End Sub


Then I have a module with the PhoneDataCopy function in it as follows:

Function PhoneDataCopy _
(ByVal Source_Row As LongPtr, ByVal Destination_Row As LongPtr)
Dim lsourceCol(1 To 5) As Integer
Dim ldestinationCol(1 To 5) As Integer
Dim x As Integer

'This sub will copy all pertinent Phone data from a row on SS tab an
copy it to a row on current tab



'Set the column references for the SS tab
lsourceCol(1) = 6 'MAC address
lsourceCol(2) = 7 'Full Name
lsourceCol(3) = 5 'NTID
lsourceCol(4) = 14 'E164 Extension Number
lsourceCol(5) = 15 'Line COS String

'Set the column reference number for the Phone Destination tab
ldestinationCol(1) = 1
ldestinationCol(2) = 2
ldestinationCol(3) = 4
ldestinationCol(4) = 5
ldestinationCol(5) = 6


'Copy data to tabs (Note: only copying the pertinent columns)
'Sheet8 is SS
For x = 1 To 5
Sheet8.Cells(Source_Row, lsourceCol(x)).Copy
ActiveSheet.Cells(Destination_Row, ldestinationCol(x)).PasteSpecia
(xlPasteValues)
Application.CutCopyMode = False
Next x



End Function




It could probably be tidied up but it does the job and I can tailor i
for the other tabs where the criteria are different.

Thanks again to all who replied

+-------------------------------------------------------------------
+-------------------------------------------------------------------