data relationship

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
Attachment 128339

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

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?"

Quote:

---

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

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?

Quote:

---

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 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?

This is very nice and really appreciating.
Everyone one should visit this thread once.