at intervals i am sent a .xls spreadsheet, with updated pricing
the data also includes cross references to equivalent products in columns F to howevermany, see sample
this workbook contains 37k lines, may vary in different versions, as shown not all lines have cross references
in many cases the a lot cross references have been dropped from later versions of the data, so i want to record all cross references from the different versions, before the information is lost
i need to either bring the xrefs from each version into the latest workbook or else possibly set up an access database, with a simple vb6 front end, and import any additional xrefs from new versions when they arrive
any suggestions for setting up database tables to link all equivalent items
or
copy all the cross reference into the current workbook, so that all the xrefs are applied to each other, when looked up from the UI sheet
note in rows 2 and 3 several of the xrefs are the same, so effectively item 2 and 3 should xref
all items in the xref columns should also be in the item column, with xrefs to the same items
i can use ADO to select all items on sheet with xrefs, but need some ideas to process the data and store
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Is there ever more than one cross reference for an item, or is it a case of "if the new record doesn't have a cross reference, use the previous cross reference?"
Is there ever more than one cross reference for an item
as seen on the image above, any number (up to 5 on the image) of xrefs per item
records on more recent sheet may have different, same, more or less xrefs (mostly less or none)
damned if i know why they drop the xrefs,
currently i have to keep opening old price lists to find xrefs then look up in the current price list
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Okay. I missed the "F to howevermany" part. So in the case of row 2 in your image, what happens if the next file comes to you with 5 xrefs also, but they're not the same 5. Is that a possibility, or no?
yes, but i would assume unlikely
different xrefs should be added and each xref should also be added to all items for reverse xref,
so all xref items in row 2 should also be on the row for each of those items, plus the item from row 2 (col B)
while later files come with some xrefs, seems to be a lot less
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
I would lean toward creating a vertical list in Excel, with a 1 to 1 relationship between "item" and "xref," then importing that into Access each time you get a new file, then comparing with both a left and a right join to see if you ever get any new xrefs. Add the new to your Access table "master" and use it to guarantee price list accuracy, if that makes sense?
Reply With Quote