Vlookup - look for unique values

[Mike_Svk]

Dear community,

I am trying to resolve following challenge.

I have fill data from one table into another one. I am using Vlookup function but it only brings the first unique value.
I need to figure out how to bring first unique value as result A1 and secong unique value as result of A2

A	B
MDRYSC2H	U92SZ086172
MDRYSC2H	U93SZ086164

Thank you for your help,
Mike

 

[etaf]

use unique() and filter()
whats the vlookup formula you are using

as you have 365 version use
Unique(filter(range of source, range of source for criteria =criteria) )

 

[HighAndWilder]

Dear community,

I am trying to resolve following challenge.

I have fill data from one table into another one. I am using Vlookup function but it only brings the first unique value.
I need to figure out how to bring first unique value as result A1 and secong unique value as result of A2

A	B
MDRYSC2H	U92SZ086172
MDRYSC2H	U93SZ086164

Thank you for your help,
Mike

Without detils of your tables, this demonstrates a technique that you can use.

=INDEX(UNIQUE(SORT(FILTER(tblData[Item],tblData[ID]=$E$2))),1)
The number at the end indicates which occurence to return.

Are you able to implement this technique?

Merge data on userform for each sheet based on multiple columns.xlsm
	A	B	C	D	E	F
1
2					3
3		ID	Item		Item 2
4		1	Item 2
5		1	Item 1
6		3	Item 2
7		3	Item 3
8		3	Item 5
9		2	Item 3
10		3	Item 3
11		4	Item 2
12		4	Item 1
13		4	Item 4
14		1	Item 4
15		4	Item 5
16		1	Item 3
17		1	Item 1
18		1	Item 1
19		2	Item 1
20		5	Item 3
21		1	Item 3
22		3	Item 3
23		5	Item 3
24		2	Item 1
25		4	Item 4
26		1	Item 4
27		4	Item 1
28		5	Item 2
29		3	Item 2
30		3	Item 4
31		5	Item 3
32		5	Item 5
33		4	Item 1
34		1	Item 2
35
Sheet1

Cell Formulas
Range	Formula
E3	E3	=INDEX(UNIQUE(SORT(FILTER(tblData[Item],tblData[ID]=E2))),1)
B4:B34	B4	=RANDBETWEEN(1,5)
C4:C34	C4	="Item " & RANDBETWEEN(1,5)

 

[Mike_Svk]

Hi guys, thank you for your replies.

I guesss I have to clarify little bit more.

I have 2 worksheets.
In first worksheet I have orders from customers. Multiple customers can order idem under specific code - MDRYSC2H
In second worksheet I have produced units based on orders from first worksheet I need to allocate for customers

A	B
MDRYSC2H	U92SZ086172
MDRYSC2H	U93SZ086164

In the end I need to allocated this
Customer 1 ordered MDRYSC2H and their order is now produced with uniqe value"U92SZ086172"
Customer 2 ordered MDRYSC2H (the same ordering code as customer 1) and their order is now produced with uniqe value"U93SZ086164"

I have 100s of orders and 1000s of produced units I have to allocate to customers.

Regards,
Mike

 

[etaf]

sorry i'm not following as i dont know what you data layout is like
using a variation of the unique(filter as posted by myself and also by HighAndWilder may do what you are after

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Mike_Svk]

Hi Etaf,

understantood.

I will try to explain with the help of uploaded google spredsheet.
( mrexcel )

First excel worksheet contains customers and what their orders ("Orders from customers") . Every customer can order multiple material codes.
Second worksheet ("Production") contains material codes and finished goods serial number

What I need to do is to find "finished product serial number" from worksheet production and paste it into worksheet Orders from customers, into C column.

The catch is that one "material" will have multiple "finished product serial numbers". For ilustration pupposes I highlated one material code with blue and the same material code with yellow. As you can see they have different "Finished product serial number" values (B3 and B8)

 

[etaf]

does this work
=IFERROR(INDEX(FILTER(Production!$B$3:$B$10,Production!$A$3:$A$10='Orders from customers'!B3),COUNTIF($B$3:B3,B3)),"not found")

mrexcel-etaf.xlsx
	A	B	C	D	E
1	Orders
2	Customer	Material	Finished product serial number	filter	Finished product serial number
3	1	MDRYSC2HK1		4U90SZ087532	4U90SZ087532
4	1	MDRX1T01B1		4U93SZ086164	4U96SZ087535
5	2	MDRX1T01B1		3rd entry to test	4U97SZ087253
6	2	MFRPSD7H4F			4U99SZ087254
7	3	MDRYSD2HK1			4U92SZ086172
8	3	MDRYSC2HK1			4U93SZ086164
9	4	MFRPSG7H4F			4U95SZ087820
10	4	MFRPSD7H4F	removed value to test error		not found
11		MDRYSC2HK1	added 3rd		3rd entry to test
Orders from customers

Cell Formulas
Range	Formula
D3:D5	D3	=TAKE(FILTER(Production!B3:B10,Production!A3:A10='Orders from customers'!B3),COUNTIF(Production!A3:A10,'Orders from customers'!B3))
E3:E11	E3	=IFERROR(INDEX(FILTER(Production!$B$3:$B$10,Production!$A$3:$A$10='Orders from customers'!B3),COUNTIF($B$3:B3,B3)),"not found")
Dynamic array formulas.

mrexcel-etaf.xlsx
	A	B
1	Productions
2	Material	Finished product serial number
3	MDRYSC2HK1	4U90SZ087532
4	MDRX1T01B1	4U96SZ087535
5	MDRX1T01B1	4U97SZ087253
6	MFRPSD7H4F	4U99SZ087254
7	MDRYSD2HK1	4U92SZ086172
8	MDRYSC2HK1	4U93SZ086164
9	MFRPSG7H4F	4U95SZ087820
10	MDRYSC2HK1	3rd entry to test
Production

link to dropbox will only be available for a few days

Dropbox

www.dropbox.com

 

[Mike_Svk]

It works, thank you

 

[etaf]

you are welcome