• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple data

sathis85

sathis85

New Member
hi,

can any one help me to find the data in excel with related multiple data


For Eg:

MRKU2215275 9060370391

MRKU2215275 9060578644

MRKU2215275 9060580450

MRKU2215275 9063176095

MRKU3367164 9063320918

MRKU3367164 9064117570

MRKU3367164 9061607008

MSKU0686160 9062314657


if i put MRKU2215275 all the data related to that cell have to list down like this

9060370391

9060578644

9060580450
 
Sathis85


have a read of: http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
HI Hui,


Thank u soooooooooooooooooooooooo much.

it is very useful to me. thanks a lot dude...........:)
 
Hi,


one more request.


for Eg:

Kindly go through this link

https://docs.google.com/spreadsheet/ccc?key=0ArEaSV4ZCBQydEdQaUpqZGdudmwtMHd6S1pnSElYR1E


in sheet 1 - if i put vendor and container no the result PO# has to display

all the related data available in sheet 2


can u help me to put formula to find PO# which relate to vendor and container/kewill/BL/ENP.......


pls help me..........
 
SirJB7, try here: https://www.dropbox.com/s/9n1qav7yd81zwn9/sat%2Cexcel.xls


Sathis85,


You only have Vendor on Sheet1 and no place for Container Number on the first page?

What data do you want returned?

Do you want all records for a particular Vendor, or just for a Vendor/Container combination ?
 
i want the combination of vendor and container, have to get the PO# which related to Vendor and container no.
 
hi all,


Kindly find the below link , now you can get what i want.


https://docs.google.com/spreadsheet/ccc?key=0ArEaSV4ZCBQydERXZmRoN2VGUmEzWUk2UXVTeVJLR0E


i want PO#, with the combination or vendor and container #


thanks for the reply Hui and Sir JB7
 
Hi, sathis85!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Multiple%20data%20-%20sat%2Cexcel%20%28for%20sathis85%20at%20chandoo.org%29.xlsx


I added a validation list to C4 cell in 1st sheet (yes, it drops down repeated values, but at least you have them, don't you?) and a helper column K in 2nd.


Be sure to make enough room for PO retrieved, copying down C9 formula as required.


Just advise if any issue.


Regards!


EDIT: I worked on previous file, didn't check your recently updated. If they differ I hope you'd adapt the changes from the older version to this last one.
 
thank u SirJB7,


one more request..........


if i put the vendor in C4 and container no in B8 and the end result what i want is PO# in C8


For eg:-


Vendor - 40

Container# - CMAU5273342

Result PO# - 9062568683


https://www.dropbox.com/sh/06odug3wzpgvcsu/M_-rKv6-rZ


vendor and container no is the key factor i want the end result will be the PO#....

pls find the above link and help me to fine a solution
 
Hi SirJB7

thanks for the reply


Pls find the below link


https://dl.dropbox.com/u/97330471/PO%20Search%202.xlsx


here i have created PO# search file with more formulas, if i search PO Document Number

in this sheet getting the po no very slow,,,,,,,,,,


can u pls advice what i did is correct or any changes will help me to do better


i am new to excel , started learning it. kindly help me to do better.


Note:-

if i put vendor that relates to container/BL/etc,,, no the end result i want is PO document no. if that container/BL/etc,,, having multiple PO doc no means that has to list down separately


pls help me,,,,,,,,,,,,,,,,,
 
Hi, sathis85!


This is a completely new workbook which only retains from previous posts the searching for a Vendor & Container arguments. Your modifications maybe logical due to your business characteristics but the formulas at Single PO Search are far more complex that what expected before last post. I haven't had the chance to fully analyze them but I suggest you the following changes:

a) don't use fixed ranges like "'PO Report'!$AA$2:$AB$1048576", instead of that use dynamic ranges as "XXXList" defined as "OFFSET('PO Report'!$AA$3,0,0,COUNTA('PO Report'!$A:$A)-2,1), you'll reduce range list from a million to 35/55K

b) you can avoid columns W:AB in sheet 'PO Report' if you replace VLOOKUP functions by combination of INDEX & MATCH functions, as in cells C8:C18 in sheet Sheet1 of my uploaded file, unless you couldn't get rid of columns AC:AG, which in fact almost always appear to display NA

c) even if you apply both previous optimizations you have built a very heavy workbook, with lots of searches and formulas that require a hugh (unbearable I should say) amount of calculation time, so I think that performance wouldn't be led to reasonable values, since SUMPRODUCT is a volatile function (that means it's always calculated, no matter if dependent cells were changed or not) and the main solution for your problem relies on it.


Regards!
 
HiSirJB7,


i made changes as you said in earlier post,,,,,, but i cant able to find the correct formula to place it in right place,,,,,,,,,,,,i don't know how to use formula like INDEX and MATCH

could u do me a favor,,,,,,,,, kindly pls change the formula from the file which i have and send back to me.............. pls pls help me,,,,,,,,,,,, i can learn from it,,,,,

https://dl.dropbox.com/u/97330471/PO%20Search%202.xlsx

pls change and upload the above file,,,,,,

Thank you so much
 
You must log in or register to reply here.
Back
Top