Scan Market components updates using vba/Vlook-up

[laxmanann]

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

[laxmanann]

adding additional info

[laxmanann]

Can anyone please help me to complete this one.

[FDibbins]

I have explained all the things in the attached word document with the snap shot(For better understanding).

why would you make members open a file just to see your request? Help us to help you by including your request IN your thread

[laxmanann]

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

[FDibbins]

OK well hopefully other members will be more inclined to open extra files

[laxmanann]

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

[noboffinme]

Hi Laxmanann,

This might be better built in MS Access.

Each Worksheet could become a table in Access & then you create joins on the fields you've described.

It's much faster & easier to manage when you've got a series of worksheets & want to do VLOOKUPS etc.

Cheers

[laxmanann]

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?

[NeedForExcel]

Hi laxmanann

As FDibbins mentioned, including your request IN your thread which is always easier to understand.

Besides, I am unable to understand your request. Can you mentioned exactly and in brief what your exact requirement is..

[laxmanann]

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

[laxmanann]

Hi All,

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

Thanks in advance

[laxmanann]

Hi,

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

Thanks

[LJMetzger]

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':

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

  Dim iRGBColor As Long

  'This removes RED color from any Row that is Double Clicked
  iRGBColor = Target.Interior.Color
  If iRGBColor = vbRed Then
    Target.EntireRow.Interior.ColorIndex = xlNone
    
    'Return Normal Focus to Excel
    Cancel = True
  End If

End Sub

Lewis

[rcm]

Try this macro

[laxmanann]

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

[LJMetzger]

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.

[laxmanann]

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.

Sub RevertToOriginalData()
  'This is used for testing purposes to reset the Worksheet data to the original state

  On Error Resume Next
  ThisWorkbook.Worksheets("Pricing").Outline.ShowLevels RowLevels:=8           'Expand (Display) the rows
  ThisWorkbook.Worksheets("Participants").Outline.ShowLevels RowLevels:=8      'Expand (Display) the rows
  ThisWorkbook.Worksheets("Item Participants").Outline.ShowLevels RowLevels:=8 'Expand (Display) the rows
  On Error GoTo 0

  ThisWorkbook.Worksheets("Pricing").Range("24:" & Rows.Count).EntireRow.Delete--->This should be 22 and not 22  
ThisWorkbook.Worksheets("Participants").Range("263:" & Rows.Count).EntireRow.Delete--->This should be 246 and not 263  
ThisWorkbook.Worksheets("Participants").Range("263:" & Rows.Count).Interior.ColorIndex = xlNone--->This should be 246 and not 263  
ThisWorkbook.Worksheets("Item Participants").Rows("7:" & Rows.Count).EntireRow.Delete--->This should be 5 and not 7  
ThisWorkbook.Worksheets("Item Participants").Columns("V:IV").EntireColumn.Delete--->This should be C:ZZ  

ThisWorkbook.Worksheets("VENDORS").Range("84:" & Rows.Count).EntireRow.Delete-->We dont need this  
  On Error Resume Next
  ThisWorkbook.Worksheets("Pricing").Outline.ShowLevels RowLevels:=1           'Collapse (Hide) the rows
  ThisWorkbook.Worksheets("Participants").Outline.ShowLevels RowLevels:=1      'Collapse (Hide) the rows
  ThisWorkbook.Worksheets("Item Participants").Outline.ShowLevels RowLevels:=1 'Collapse (Hide) the rows
  On Error GoTo 0
    
  On Error Resume Next
  Sheets("Sheet1").Select
  On Error GoTo 0
End Sub

Thanks in advance LJ

[LJMetzger]

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:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Change Group (Outline) on Sheets
'
'Set the CONDITIONAL COMPILATION CONSTANT below to 'True'  to have GROUPS 'include' DATA
'Set the CONDITIONAL COMPILATION CONSTANT below to 'False' to have Groups 'exclude' DATA
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#Const NEED_DATA_IN_GROUPS = False   'Set this to 'True' or 'False' as required
#If NEED_DATA_IN_GROUPS = True Then
  'Add Data to the Groups in additional to the Headers
  On Error Resume Next
  
  iLastRow = wsPricing.Range("A:A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  wsPricing.Rows("3:" & iLastRow).OutlineLevel = 2
  
  iLastRow = wsParticipants.Range("A:A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  wsParticipants.Rows("3:" & iLastRow).OutlineLevel = 2
  
  iLastRow = wsItemParticipants.Range("A:A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  wsItemParticipants.Rows("3:" & iLastRow).OutlineLevel = 2
  
  On Error GoTo 0
#Else
  'Headers ONLY in Groups
  On Error Resume Next
  wsPricing.Rows("3:17").OutlineLevel = 2
  wsParticipants.Rows("3:245").OutlineLevel = 2
  wsItemParticipants.Rows("3:4").OutlineLevel = 2
  On Error GoTo 0
#End If

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

Lewis

[laxmanann]

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.

[laxmanann]

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

[LJMetzger]

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

[laxmanann]

Dear LJ,

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