Lookup a value and return multiple results for it

[mp3909]

Hi,



I have this data in excel that is at least 200,000 rows and when I enter formulas, it takes very long to compute.
Given that I am working with quite a big dataset, I was wondering to do this in Access.

So I have a list of values in column A.
Column D contains a unique list of values in Column A i.e. removal of duplicates.
Column E and so forth returns the values that are in Column B when looking up the value in Column D against Column A.

So here is a snapshot of what I have described so far:




Can this be achieved in access and will it be efficient to do for a data set of 200k+ rows?

Thank You.

[Bulzie]

That data doesn't look right, wouldn't Kiwi for E and F be mouse and monitor? Looks like you are grabbing the value for one row above? Or am I looking at that wrong.

You can do most things in Access as long as you set up the data table structures correctly. Is there a hard limit to the number of values in Column B for a given A fruit?

[mp3909]

That data doesn't look right, wouldn't Kiwi for E and F be mouse and monitor? Looks like you are grabbing the value for one row above? Or am I looking at that wrong. Sorry, you are correct. This is how its meant to look:



You can do most things in Access as long as you set up the data table structures correctly. Is there a hard limit to the number of values in Column B for a given A fruit? No, there is no hard limit to the number of values in Column B

Thank You in advance