Wednesday, September 21, 2016

Troubleshoot:Complex Vlookup or other? Error

Complex Vlookup or other?

I am trying to find a matching part number between two pages.  However, each page may have many occurances of the same part number with different quantities of each item on different drawings.  Is there anyway I can use a complex vlookup in order to identify
the corresponding part number and add up the associated quantites and call out each drawing number?
 
Sheet 1
Part number               qty        Drawing Number
M17/19-RG179
 
I want to see if sheet two has the same part number, if so, what quantity total and what drawing numbers
 
Sheet 2
Part number                qty        Drawing number
M17/19-RG179          11              4320
RF-082-4380               75               5280
M17/19-RG179              44          4355,4280
 
Each entry being a different cell except the last drawing number.  
 
So what I would like to see with the query is this:
 
Sheet  1               
Part Number               qty                  Drawing Number
M17/19-RG179          55                    4320, 4355, 4280
 
 
Would it be easier to use Access?

Solutions to the Problem Complex Vlookup or other?

Download SmartPCFixer for Free Now

Hi,
 
First need to insert User Defined Function...

Open up your workbook.
Get into VB Editor (Press Alt+F11)
In the Project window, Insert a new module (Insert > Module)
Copy and Paste the code (given below)
Get out of VBA (Press Alt+Q)
Save your Workbook as Excel Macro-Enabled workbook.
 
Here is the code...
'==================================================================
Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Rickson, MrExcel MVP
    ' used as exactly like COUNTIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex.
=ConcatIf($A$1:$A$10,C1,$B$1:$B$10,", ",True)
Dim i As Long, j As Long
With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With
If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
   
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function
'==================================================================
 
Assuming in Sheet2, Part number is in A Column, Qty is in B Column, Drawing number is in C Column.
 
Then in Sheet1, enter this in A2...
M17/19-RG179
(List of Part#)
 
Then enter this in B2...
=SUMIF(Sheet2!$A$2:$A$1000,$A2,Sheet2!$B$2:$B$1000)
 
Then this in C2...
=ConcatIf(Sheet2!$A$2:$A$1000,$A2,Sheet2!$C$2:$C$1000,", ",TRUE)
 
Copy B2 and C2 and paste downward...
 
UDF is from this link, thanks to them...
http://www.excelforum.com/excel-general/680160-returning-a-multiple-result-lookup-to-a-single-cell.html
 
Hope it helps,
Jaeson

  • Download the Microsoft Windows ISO (installation device) from the following website:
  • http://www.microsoft.com/en-us/software-download/windows10
  • Users must select the appropriate ISO, either 32 bit or 64 bit, depending upon their individual computer.
  • Extract the ISO file to a separate USB device or burn the program to a compact disc.
  • Run the Microsoft Windows 10 upgrade directly from the software that you have thus created.

Another Safe way to Fix the Problem: Complex Vlookup or other?:

How to Fix Complex Vlookup or other? with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your computer.  Open it, and it will scan your computer. The errors will be shown in the scan result.

2. After the scan is done, you can see the errors and problems which need to be fixed.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been removed.


Related: Best Way to Download Satellite L645-SP4004M WinDVD BD for Toshiba v.10.0.6.110 driver,Download Toshiba Satellite C655D-S5057 Wireless LAN Driver v.8.0.0.258.0,Method to Update & Download Toshiba Satellite L650-BT2N22 Web Camera Application v.2.0.3.37 driver,How Can You Update & Download Toshiba Satellite L775D-S7220GR Value Added Package v.1.6.0130.640204 driver,Download Toshiba Satellite Pro L750-SP5176FM Sleep Utility v.1.4.0025.000101 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: How to Fix Error - Conditional Formtting 2007 If Statement?,computer runs very slow,freezes up andkeyboard doesn'twork Tech Support,How to Fix - Computer randomly freezing after fresh install?,[Anwsered] connect to wireless network using command promote,How to Resolve - Conditional format one cell based on text in another?,Can't change homegroup sharing permission,Can't connect XP shared printer to Win7 except as a local printer.__,Can You Connect the LAN Adapter for Wii On your Windows 7 Laptop?,Can These Files Be Deleted,Can’t install Microsoft Flight Simulator X Gold Edition error 1722

No comments:

Post a Comment