Listing matching results from a table in one cell

[clive2002]

I have to normalised data lists, i want to effectively de-normalise (if thats a word).

I've attached an example which will hopefully clarify.

But i have a table of products and want to list attachments which match each product id, in one cell.

 

[unmarkedhelicopter]

Clive2002
It would be nice if you could actually send some test data, i.e. you could paste the areas into word as tables instead of pictures, better still zip the XLS and post that.

de-normalise is not a real word but I think we get your drift, in fact if the data were normalised and residing in a database it would in all likelyhood look like your required output. (maybe reversed depending on any other links). Because you should never store more than one thing in one field.

Having looked at your data it seems to be that maybe a redefinition of the problem may be in order (to ensure I've got the right end of the stick).

You have a 3 column input ; Product Key, Avaliable Attachments and Attachement List

Attachment List : contains a list of Attachments for a Given Product Key, separated by commas
Available Attachments : may be ignored as what are you going to do if this says 20 and there are only 18 items or visa versa ?

You don't say if we can delete the original list or where you want the new list (which will have the Product Key repeated until no more attachments for that Product exist) do you want this as a new sheet or offset from the original list (to the right or below) ???

 

[clive2002]

sorry dude,

guess my explaination was a bit short, but youve got it the wrong way round.

What i want is represented by the third column of the example (attachment list).

I have the data in the normalised table, i want a UDF which will return a list of matching attachments for that product.

A bit like a countif() only rather than counting matches it would list all attachmentid entries with a matching productID input.

 

[unmarkedhelicopter]

Okay ...
test data ???

 

[chergh]

If your table can be in Access you may be able to do it something like this:

dim strSQL as string
dim db as database
dim attach as string
dim rs as recordset
dim path as string
dim i as int

path = where_the_db_is

set db = workspaces(0).OpenDatabase(path, ReadOnly:=False)

strSQL = "SELECT tbl_name.attachment FROM tbl_name " & _
"WHERE tbl_name.product_key = Prod_key_source"

set rs = db.openrecordset(strSQL)

rs.movefirst

i =1

do while not rs.eof

if i = 1 then

attach = rs!attachment

i = i + 1

else

attach = attach & ", " & rs!attachment"

end if

Loop

wb.ws.cells(1, 1).value = attach