Forum Discussion
How to find a specific number from 2 columns
| Source | number | Wanted outcome | Question: |
| a | 1 | Number is only from source a | I like to know from what source is the number coming from? |
| a | 1 | Number is from source a and b | Is it alone from source a, alone from source b, or is it in both? |
| a | 2 | Number is from source a and b | Watch out: it is possible that a number is twice or more from the same source |
| a | 3 | Number is from source a and b | |
| a | 4 | Number is only from source a | I have over 6000 rows so it is hard to do it by hand, how can i figure this out in Excel? |
| b | 2 | Number is from source a and b | Thnx, Eric. |
| b | 2 | Number is from source a and b | |
| b | 3 | Number is from source a and b | |
| b | 5 | Number is only from source b | |
11 Replies
One more option is PivotTable with measure
Sources:=CONCATENATEX( DISTINCT( Table1[Source] ), Table1[Source], ", " )which gives
Depends on Excel version. But since nobody knows on which Excel version/platform your are, other suggested solutions as well could work, could not work.
This is a 365 insider beta solution to the problem, so irrelevant to most!
= LET( nums, UNIQUE(number), src, MAP(nums, LAMBDA(n, TEXTJOIN(", ",, UNIQUE(FILTER(source, number=n))) ) ), CHOOSE({1,2}, nums, src) )erictabak You mentioned that your example is just a simplification of the real problem. By doing so, you may get the answer to your question, which, most likely is not going to be the solution to your problem. Based on your description alone, I would first think of a simple pivot table, counting the number of occurrences of each number per source. It's difficult to imagine that you really want 6000 rows with sentences like in your example with sources that probably aren't just "a" and "b".
Alternatively, I would go for Power Query, to avoid great numbers of complicated formulae. It's demonstrated in the attached file. But, of course, based on your simplified example.
- this looks like a possible outcome aswell, it is a shorter route. I have to translate the formula in dutch excel, so i can't say if it's working well at this moment. Thank you for helping me.
=IF(AND(NOT(ISNA(VLOOKUP("a"&B14,$A$14:$A$22&$B$14:$B$22,1,FALSE))),NOT(ISNA(VLOOKUP("b"&B14,$A$14:$A$22&$B$14:$B$22,1,FALSE))))=TRUE,"Number is from source a and b",IF(NOT(ISNA(VLOOKUP("a"&B14,$A$14:$A$22&$B$14:$B$22,1,FALSE)))=TRUE,"Number is only from source a",IF(NOT(ISNA(VLOOKUP("b"&B14,$A$14:$A$22&$B$14:$B$22,1,FALSE)))=TRUE,"Number is only from source b")))
Is this what you want to do? I understand that there are only 2 possible sources a and b and there can be numbers 6, 7, 8 and so on as well.
- Thanks, yes this is what i need i guess. It looks like it will work. I have to translate the formula in dutch excel. Many thanks.
Why does 1 come only from source a and also from source a and b?
- it's a simplifiction of the real problem. 1 can come from both sources.
- I could not understand.
Your outcome is very confuse.
Why the numbers 2 and 3 the outcome is "Number is from source a and b"???Juliano-Petrukio It is a simplification of the real problem, it is what it is. I have two sources and from both its possible to have the same numbers in it, sometimes it is and sometimes it only one of them.
Thanks for your thinking anyway.Eric
