search table for unique values return values and corresponding cells to a different sheet

**thara95** · 02-28-2017, 09:51 AM

Hi guys - I have been dabbling with vba for a little while now and normally I manage to find the answer pretty easily but with this one I am struggling (also been googling for days)

so in my datasheet I have a table in the following format

a b c d e f g
1 aa ab ac af aa af 01/02/2016
2 aa bb ff fc aa ff 13/05/2016
3 gg ff aa af aa ff 17/11/2016

(don't shout at me I haven't worked out how to put it in properly yet)

A1:F3 contain a varity of codes, (aa,ab, etc) and column G contains a date.

I need a way to get the first instance of each unique value along with the corresponding date in a list on a different sheet (reportsheet2)

hopefully looking something like this

a b
1 aa 01/02/2016
2 ab 01/02/2016
3 ac 01/02/2016
4 af 01/02/2016
5 bb 13/05/2016
6 ff 13/05/2016

there is wayyyyyy more data than I have provided in this example just as a heads up

I do have a little bit of code that has arranged the rows into date order but other than that I am stuck,

any help would be greatly apreciated,

Kind regards,

T

**BellyGas** · 02-28-2017, 10:08 AM

If you've got the data in date order....and you're looking for the first instance of each value (you say unique value but I don't think you mean that, from your results it looks like you want the first time every value appears, not just the unique ones).....then:

Use a for next loop to cycle down through your data, on every row you stop and compare the value in column B with all the values in the list you're making, if a value is not in the list then add it together with the date from column G. If a value appears more than once then it'll already be in the list when your cycle reaches the second instance.

**thara95** · 02-28-2017, 10:44 AM

I think I mean Distinct?

And how would I do that?

really sorry I am properly stuck on this

Kind regards,

Thara

**thara95** · 02-28-2017, 10:55 AM

I currently have this and after that I am massively stuck

Please Login or Register  to view this content.

**BellyGas** · 02-28-2017, 11:02 AM

You don't want to scan the whole row, just the column with the data in.

so...

Please Login or Register  to view this content.

**thara95** · 02-28-2017, 11:46 AM

thanks,

Why doesn't it like the first bit

Please Login or Register  to view this content.

and guessing I add this for all Columns?

Kind regards,

T

**thara95** · 02-28-2017, 12:14 PM

it doesn't like the range?

**BellyGas** · 03-01-2017, 08:44 AM

range("ThisSheet!A1048000")

is your worksheet actually named "ThisSheet"? Change it to the actual sheet name and it'll work.

**thara95** · 03-01-2017, 09:04 AM

i changed it to the actual sheet number...

search table for unique values return values and corresponding cells to a different sheet

LinkBack

Thread Tools

Rate This Thread

Display

search table for unique values return values and corresponding cells to a different sheet

Re: search table for unique values return values and corresponding cells to a different sh

Re: search table for unique values return values and corresponding cells to a different sh

Re: search table for unique values return values and corresponding cells to a different sh

Re: search table for unique values return values and corresponding cells to a different sh

Re: search table for unique values return values and corresponding cells to a different sh

Re: search table for unique values return values and corresponding cells to a different sh

Re: search table for unique values return values and corresponding cells to a different sh

Re: search table for unique values return values and corresponding cells to a different sh

Thread Information

Users Browsing this Thread

Similar Threads

Search table to return multiple values

Search for specific values in a column, and return values of another range of cells

[SOLVED] Using INDEX MATCH to return unique values for non-unique search term

Formula to search for repeat values and return unique information

Need to fill blank cells with values from another sheet that match unique values in rows

return all distinct unique values to another sheet if it exists in the other worksheets.

Return Multiple Unique Values for Horiontal Table

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions