Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Something like vLookup for duplicate values on other tab
Hi,
I need to do something like vLookup which will populate calls on one tab with information from another tab where the criteria will be matched in more than one row on the other tab without having blank lines on the destination tab. Can anyone help or am I being too ambitious? |
#2
|
|||
|
|||
Quote:
Not too ambitious at all. Perhaps you could post a dummy workbook with some examples of what you need as results included? |
#3
|
|||
|
|||
Quote:
Main Data is in tab SS In tab '7945 Users' I need to populate cells in green columns with data 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 data from SS tab if (in SS tab) ColumnC = 7945 and ColumnH = No Hope this is enough to get you started. Thanks |
#4
|
|||
|
|||
Quote:
Basically, there are two ways of doing this that I can think of. One involves some quite complex (and by that I mean awkward to maintain when data requirements change) formulas. The other involves some hidden "helper columns" that are nice and easy to maintain. |
#5
|
|||
|
|||
Quote:
Hidden columns would be fine as long as they are to the right of the existing data as I will need to copy paste blocks of the data to another spreadsheet. Data in the SS tab will change completely from job to job but the columns will not. The other tabs will stay the same throughout if that helps any. Thanks |
#6
|
|||
|
|||
Quote:
Would it be possible to do the data copy using something like this? Pseudo code for populating tabs To run on entry to a tab -- Worksheet_Activate() ??? Start{ Do you want to update tab data? no = end yes = continue current_line = 2 [which row to start data paste on] for ss = 2 to 3000 [no of lines in SS tab with data] if SS_tab_cell C'ss' = 7945 and SS_tab_cell H'ss' = Yes then copy SS_tab_cell F'ss' and paste value to current_tab_cell A'current_line' copy SS_tab_cell G'ss' and paste value to current_tab_cell B'current_line' copy SS_tab_cell E'ss' and paste value to current_tab_cell D'current_line' copy SS_tab_cell M'ss' and paste value to current_tab_cell E'current_line' copy SS_tab_cell N'ss' and paste value to current_tab_cell F'current_line' current_line = current_line+1 next else next endif }End |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something like vLookup for duplicate values on other tab
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something like vLookup for duplicate values on other tab
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 formula starts 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 the Index 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 |
#9
|
|||
|
|||
Quote:
I had the most success with the code. The first formula for the index worked fine, but I had issues with the second one to go in the target tab. It kept trying to open new workbooks and the data did not get pulled across. I will try and split up your code so I can have a set of tab specific subs to do the data transfers. I also want to try and get the data without the blank lines, more on the lines of my Pseudo code, as I ended 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 to change it much once it is working. Notice what an optimist I am ;) I will let you know how I get on. |
#10
|
|||
|
|||
Thanks again Ben for your inspiration to kick me off :)
I have added the following code to the TAB code page and it transfers the correct data with no blank lines. All I need now is an easy way to manually trigger the sub without going in to the developer ribbon. Is it possible to add something to the Data ribbon which will run this 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 to 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 Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something like vLookup for duplicate values on other tab
I'm happy to help and glad to hear that things seem to be heading the right direction. Best of luck.
Ben |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something like vLookup for duplicate values on other tab
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/libr.../ee767705.aspx. Ben |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something like vLookup for duplicate values on other tab
Hi
You could try triggering it when you open the workbook. Private Sub Workbook_Open() Call ParseData End Sub HTH Mick. |
#14
|
|||
|
|||
Quote:
The issue I have is that each sheet has a sub ParseData() and the criteria for the data and the cells copied can be different for each sheet. I did think about the button route as I have used that before to sort result league tables after they have been updated. i will look into that link you provided to see if I can get the equivalent of the button but not embedded on the sheet. I do not think I will have much success though after thinking about it. Still worth a try. |
#15
|
|||
|
|||
Quote:
I currently have a copy of the ParseData code under Private Sub Worksheet_Activate() with a check so it will only run if there is no data in the sheet. What I need now is a manual update option. I did think about adding a dialog at the start of the Worksheet_Activate() to ask if an update is required and then remove the 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 buttons but how to get this to display and then receive the information on which button is pressed is beyond me. I was thinking that one button would return true and the other false. |
#16
|
|||
|
|||
Quote:
Here is my current solution, which also has the manual update thing 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 to 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 to 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 and 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)).PasteSpecial (xlPasteValues) Application.CutCopyMode = False Next x End Function It could probably be tidied up but it does the job and I can tailor it for the other tabs where the criteria are different. Thanks again to all who replied. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP DUPLICATE VALUES | Excel Worksheet Functions | |||
Vlookup function returns duplicate values | Excel Discussion (Misc queries) | |||
Vlookup for multiple duplicate numerical values | Excel Worksheet Functions | |||
Why does this code remove Duplicate Values, by showing only 1, but it does NOT show Unique values for some reason ? | Excel Programming | |||
problems with displaying "duplicate vlookup values" in same column | Excel Discussion (Misc queries) |