Scan Market components updates using vba/Vlook-up

Hi All,

Have a good day.After a long long time i'm here came for help from experts.Based on this forum's guide now i can able to tackle many things.Thanks for all the experts. The below is my new thread.

I have did some alignment and vlook up,finally i have designed this workbook attached <Coach Auction 04082015_test1.xls>.
BUt i need to make some sync up's to get the expected data forms.I have explained all the things in the attached word document with the snap shot(For better understanding).

Macrospec.ppt: I made a ppt which more or less tries to explain what needs to be done. Hopefully it is logically correct. My programming skills are quite basic, so there is no guarantee it is ok.
Coach Auction 04082015_blank2.XLS: This sample should be the blank worksheet
Coach Auction 04082015_test1.XLS: This sample should be the output.


The SCANMARKET (records marked in green as examples) / VENDORS tabs will the standard input

Thanks in advance

adding additional info

Can anyone please help me to complete this one.

Dear FDibbins,
Thanks for your advice.That attachement contains snapshot,arrow mark highlights.so I kept that pictorial explanation in word doc.kindly bare with me.thanks

Dear FDibbins,

Kindly tell me what i have to do.Im not aware of that.can you please give me an idea to sort out my problem here.Thanks in advance

Dear Nob,

Thanks .we dont use Ms-Access for taking these kind of reports.It's i need to do with excel vba macro only.Is it not possible in excel?

As Per Experts advice am just adding the some points here.

The Coach Auction 04082015_test1.XLS will be the output sheet:

1.First the P251750168 needs to go to the pricing tab (if not already present)

Note: In the pricing tab the Type,Name,Bid on Unit,Commodity,Bid Improvement,Column K,Column L are the constants and that is same for all rows

2.Then the same P251750168 needs to go to the “Item Participants” tab (If not already present)


3.Then we need to check If the invited vendor (P990205) against P251750168 is already on the “Participants” sheet or not.
We need to Vlookup the P990205 in the VENDORS tab and return the email address


4.If not there, you need to copy the email address and the name

5.Then we need to also place the vendor to the item participants tab, and also place an “Invited” string behind that item against which the vendor is invited

Thanks

Hi All,

do we have any update on my Question.Kindly help me to complete this activity.

Thanks in advance

Hi,

can anybody give me a heads up for this..am waiting for the positive reply.

Thanks

Hi,

Try the attached workbook. The code is too long to display here.

How the software works (simplified):
a. Read all the Items from Column 'A' (BK REF) of Sheet 'SCANMARKET' and save each UNIQUE Item.
Read all the Items from Column 'C' (SUPPLIER) of Sheet 'SCANMARKET' and save each UNIQUE Item.

b. Put the UNIQUE Items (BK REF) in Column 'D' of Sheet 'Pricing' if not already there.
Add initial data to other columns of Sheet 'Pricing'.

c. Put the UNIQUE Items (BK REF) in Column 'B' of Sheet 'Item Participants' if not already there.
Put the Number (e.g. 4.22) in Column 'A' of Sheet 'Item Participants'.

d. Use the 'Supplier Code' to get the Supplier Email address and Supplier Name from the 'VENDORS' Sheet.
Add the SUPPLIER Email address and the SUPPLIER Name to Sheet 'Participants' if not already there.
Add a WARNING Data Line to the 'Participants' Sheet if the 'Supplier Code' is NOT on the 'VENDORS' Sheet.

e. Process each line item in Sheet 'SCANMARKET'. Place 'Invited' on Sheet 'Item Participants' at the intersection of BK REF' and 'Email Address for Each Line Item on Sheet 'SCANMARKET'.

NOTE:
1. Rows in Sheet 'Participants' are colored RED if the Supplier Code is NOT on Sheet 'VENDORS'.
2. Cells in Sheet 'Item Participants' are colored GREEN each time 'Invited' is ADDED to that cell.


Items of concern:
a. Missing items e.g. Mayne Coaches vs Mayne Coaches Ltd.
b. There is no check for duplicate items on Sheet SCANMARKET.

You should add the following code to the ThisWorbook module of your file if you want to remove RED from rows each time a cell in a 'RED' row is 'Double Clicked':

Please Login or Register  to view this content.

Lewis

Try this macro

Dear Lewis,

Sorry for the delay.i Just came back from weekend and i din have any internet access for this weekend.
I just checked your macro file and i dont have any words to say thanks.My Total manual efforts is reduced
with your code.All are the items are looks very very good and it is working fine.One or two things small problems i have now.

1.The revert to Original Data starts the purge from the wrong rows. I am sending you a sample file. This is how the empty file should look like.

2.The participants on the “participants” sheet are grouped. Therefore they are hidden. I am not sure why. Is it possible to easily fix this? (easily I mean that you know how to do it and you can do it in 5 minutes)

3.Same with the “Item participants”

unnamed.png



4.This is something I forgot, I am sorry. Is is possible for us to fill in the rest of the values “Uninvited” on the Item Participants tab (With this code)?


That's all:

Items of concern:


a. Missing items e.g. Mayne Coaches vs Mayne Coaches Ltd.
This won’t be a problem. That sheet is not complete yet
b. There is no check for duplicate items on Sheet SCANMARKET.
There shouldn’t be any dupe rows. If there is, SKIP.


Thanks agian,
Lax

Thanks for the rep points.

1.The revert to Original Data starts the purge from the wrong rows.

The 'Revert to Original Data' was built to go back to the original state for your file that already had some data in it, as a tool during software development.

If you want something like that for PERMANENT USE, I need to know what specific rows to clear on which sheets. If there is Group data, do you want the Group data visible or hidden?

2.The participants on the “participants” sheet are grouped. Therefore they are hidden. I am not sure why. Is it possible to easily fix this? (easily I mean that you know how to do it and you can do it in 5 minutes)

I don't understand the request. I don't know if you want me to group the data, or if you want me to remove the grouping from the data. Please be more specific with your request.

3.Same with the “Item participants”

See response to item 2 above.

4.This is something I forgot, I am sorry. Is is possible for us to fill in the rest of the values “Uninvited” on the Item Participants tab (With this code)?

'Uninvited' will be added.

Hi Lewis,
The RevertToOriginal doesn’t make the sheets blank. It reverts the sheets as it was in my example sheet.We need to do the below highlighted thing and then modify the UpdateSupplierInformation() in accordance to this as well.

Please Login or Register  to view this content.

Thanks in advance LJ

Hi Lax,

I made the following changes in the attached file:
a. Changed row numbers in RevertToOriginalData() per post #18.
b. Added 'Uninvited to Sheet 'Item Participants'.
c. Only Headers are included in Groups.


To add data to Groups, set the CONDITIONAL COMPILATION CONSTANT to 'True' on line 660 in Module ModCoachAuction. Line number is approximate (was correct when this was written).

Code Excerpt for CONDITIONAL COMPILATION CONSTANT:

Please Login or Register  to view this content.

NOTE: SCANMARKET duplicate on lines 146 and 147 (P990040 BETTER MOTORING SERVICES)

Lewis

Hello Lewis,
Thanks ,this worked well.you have helped me for many vba projects.In that nothing has been failed so far.You are awesome.Hats off you.

index.png

Hello Lewis,

Good day for you.In this project everything is working fine.But i am struck with new update.Kindly have a look and help me out.The requirement is below..

-->Currently the macro works in a way that it only finds the first “Pxxxxxx”, but I would need all of them



-->In this example below I want to macro to add both P990053 rows, not just the first one.

Thanks in advance..Waiting for you..

Lax

Hi,

The software now processes all matching e-mail addresses for a P number. Please let me know if I implemented what you wanted. See the attached file.

There were two changes I had to make:
a. When searching for a P Number, I continued searching until there were no more matches. Previously I stopped searching after the first match. All e-mail addresses are stored in the E-mail dictionary, separated by SPACES.
b. When implementing 'Invited', the E-mail address variable had to be parsed to extract all the E-mail addresses for a specific P Number.

Lewis

Dear LJ,

The results are awesome ..perfect touch..Thank you so much guru..