Styjun

How does this piece of code determine array size without using sizeof( )?

What would a Dragon have to exhale to cause rain?

How can I fix the label locations on my tikzcd diagram?

He is the first man to arrive here

Pedaling at different gear ratios on flat terrain: what's the point?

Cannot remove door knob -- totally inaccessible!

What is the velocity distribution of the exhaust for a typical rocket engine?

How was the blinking terminal cursor invented?

How can I safely determine the output voltage and current of a transformer?

Why did nobody know who the Lord of this region was?

What technology would Dwarves need to forge titanium?

Holding rent money for my friend which amounts to over $10k?

Is there any deeper thematic meaning to the white horse that Arya finds in The Bells (S08E05)?

I recently started my machine learning PhD and I have absolutely no idea what I'm doing

FIFO data structure in pure C

Why would you put your input amplifier in front of your filtering for and ECG signal?

How to handle professionally if colleagues has referred his relative and asking to take easy while taking interview

Can a person still be an Orthodox Jew and believe that the Torah contains narratives that are not scientifically correct?

Why use a retrograde orbit?

When the match time is called, does the current turn end immediately?

Why would company (decision makers) wait for someone to retire, rather than lay them off, when their role is no longer needed?

How could it be that 80% of townspeople were farmers during the Edo period in Japan?

How can I make dummy text (like lipsum) grey?

Rushed passport - does my reason qualify?

How to lookup if my lookup data has duplicate values?

Select the first instance of a recordHow do I find duplicate values in a table in Oracle?How can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerCan I concatenate multiple MySQL rows into one field?SQL update query using joinsSQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?How to delete duplicate rows in sql server?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I am trying to lookup values from Table 1 to Table 2 based on Col1 in Table 1.

The catch is that Table 1 has duplicate values (for example, A is repeated 3 times) but I don't want to duplicate the returned value from Table 2.

How can this be done through either excel or sql (e.g. LEFT JOIN)?

sql excel-formula

share|improve this question

edited Mar 23 at 16:36

Gordon Linoff

810k37324431

asked Mar 23 at 16:34

biz_wbiz_w

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How would you, in your example, link the number to a color?
  
  – Kars
  Mar 23 at 16:39
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why is there 0 in Col3 instead of 100 or 150? When do you want to get 0 for this column?
  
  – Pavel Smirnov
  Mar 23 at 16:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This can't be done using one SQL statement you have to use DML statement as well
  
  – Kedar Limaye
  Mar 23 at 16:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I should clarify that col2 (color information) isn't relevant to the lookup. I am just trying to lookup based on col1. So if we did a regular vlookup in excel, every row where col1 = A would have 100 value and every row where col1 = B would return 150. I was wondering if it's possible to do a lookup based on unique values in col1 (e.g. 100 shows up once for A rows, 150 shows up once for B rows). Hence the 0 values for col3. Hope this explains it better.
  
  – biz_w
  Mar 23 at 21:18
  

  
  
  
  

add a comment | 

0

I am trying to lookup values from Table 1 to Table 2 based on Col1 in Table 1.

The catch is that Table 1 has duplicate values (for example, A is repeated 3 times) but I don't want to duplicate the returned value from Table 2.

How can this be done through either excel or sql (e.g. LEFT JOIN)?

sql excel-formula

share|improve this question

edited Mar 23 at 16:36

Gordon Linoff

810k37324431

asked Mar 23 at 16:34

biz_wbiz_w

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How would you, in your example, link the number to a color?
  
  – Kars
  Mar 23 at 16:39
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why is there 0 in Col3 instead of 100 or 150? When do you want to get 0 for this column?
  
  – Pavel Smirnov
  Mar 23 at 16:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This can't be done using one SQL statement you have to use DML statement as well
  
  – Kedar Limaye
  Mar 23 at 16:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I should clarify that col2 (color information) isn't relevant to the lookup. I am just trying to lookup based on col1. So if we did a regular vlookup in excel, every row where col1 = A would have 100 value and every row where col1 = B would return 150. I was wondering if it's possible to do a lookup based on unique values in col1 (e.g. 100 shows up once for A rows, 150 shows up once for B rows). Hence the 0 values for col3. Hope this explains it better.
  
  – biz_w
  Mar 23 at 21:18
  

  
  
  
  

add a comment | 

I am trying to lookup values from Table 1 to Table 2 based on Col1 in Table 1.

The catch is that Table 1 has duplicate values (for example, A is repeated 3 times) but I don't want to duplicate the returned value from Table 2.

How can this be done through either excel or sql (e.g. LEFT JOIN)?

sql excel-formula

share|improve this question

edited Mar 23 at 16:36

Gordon Linoff

810k37324431

asked Mar 23 at 16:34

biz_wbiz_w

1

share|improve this question

edited Mar 23 at 16:36

Gordon Linoff

810k37324431

asked Mar 23 at 16:34

biz_wbiz_w

1

share|improve this question

edited Mar 23 at 16:36

Gordon Linoff

810k37324431

asked Mar 23 at 16:34

biz_wbiz_w

1

edited Mar 23 at 16:36

Gordon Linoff

810k37324431

edited Mar 23 at 16:36

Gordon Linoff

810k37324431

asked Mar 23 at 16:34

biz_wbiz_w

1

asked Mar 23 at 16:34

biz_wbiz_w

1

1 Answer
1

active

oldest

votes

0

What SQL are you using? Are you familiar with CTE and partition?
Have a look here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/597b876e-eb00-4013-a613-97c377408668/rownumber-and-cte?forum=transactsql
and here: (answer and 2nd comment): Select the first instance of a record

You can use those ideas to create another field that tells you whether the row is the first, 2nd , 3rd etc occurrence of Col1. Eg you'd have something like
1 B Red 150
2 B Red 150

and you can then update col3 to be zero where this new field is not 1.

EDIT: since you asked about Excel: in Excel, sort by whatever criteria you may need (col 1 first, of course). Let's say that Col1 starts (excluding the heading) in cell C2. Set cell B2 =1. Then write this formula in cell B3:

=IF(C3=C2,B2+1,1)

and drag it all the way down. This will count the occurrences of col 1, ie it will tell you which is the first, 2nd etc time a given value appears in col1. You can then use it as as the basis to change the value in other columns.

Also, it is not good practice to have a column where the first cell has a different formula from the others. You can use the same formula nesting another IF and referencing the row, so as to set one formula for the first row and one for the others.

share|improve this answer

edited Mar 23 at 21:43

answered Mar 23 at 17:19

Pythonista anonymousPythonista anonymous

1,52662658

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm using Teradata SQL. Interesting, I am not familiar with partition. Sounds like it could work. Also, if this can be done in regular excel with some sort of adjusted vlookup or index/match that works for me too.
  
  – biz_w
  Mar 23 at 21:19
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55315946%2fhow-to-lookup-if-my-lookup-data-has-duplicate-values%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

What SQL are you using? Are you familiar with CTE and partition?
Have a look here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/597b876e-eb00-4013-a613-97c377408668/rownumber-and-cte?forum=transactsql
and here: (answer and 2nd comment): Select the first instance of a record

You can use those ideas to create another field that tells you whether the row is the first, 2nd , 3rd etc occurrence of Col1. Eg you'd have something like
1 B Red 150
2 B Red 150

and you can then update col3 to be zero where this new field is not 1.

EDIT: since you asked about Excel: in Excel, sort by whatever criteria you may need (col 1 first, of course). Let's say that Col1 starts (excluding the heading) in cell C2. Set cell B2 =1. Then write this formula in cell B3:

=IF(C3=C2,B2+1,1)

and drag it all the way down. This will count the occurrences of col 1, ie it will tell you which is the first, 2nd etc time a given value appears in col1. You can then use it as as the basis to change the value in other columns.

Also, it is not good practice to have a column where the first cell has a different formula from the others. You can use the same formula nesting another IF and referencing the row, so as to set one formula for the first row and one for the others.

share|improve this answer

edited Mar 23 at 21:43

answered Mar 23 at 17:19

Pythonista anonymousPythonista anonymous

1,52662658

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm using Teradata SQL. Interesting, I am not familiar with partition. Sounds like it could work. Also, if this can be done in regular excel with some sort of adjusted vlookup or index/match that works for me too.
  
  – biz_w
  Mar 23 at 21:19
  
  

  
  
  
  

add a comment | 

0

What SQL are you using? Are you familiar with CTE and partition?
Have a look here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/597b876e-eb00-4013-a613-97c377408668/rownumber-and-cte?forum=transactsql
and here: (answer and 2nd comment): Select the first instance of a record

You can use those ideas to create another field that tells you whether the row is the first, 2nd , 3rd etc occurrence of Col1. Eg you'd have something like
1 B Red 150
2 B Red 150

and you can then update col3 to be zero where this new field is not 1.

EDIT: since you asked about Excel: in Excel, sort by whatever criteria you may need (col 1 first, of course). Let's say that Col1 starts (excluding the heading) in cell C2. Set cell B2 =1. Then write this formula in cell B3:

=IF(C3=C2,B2+1,1)

and drag it all the way down. This will count the occurrences of col 1, ie it will tell you which is the first, 2nd etc time a given value appears in col1. You can then use it as as the basis to change the value in other columns.

Also, it is not good practice to have a column where the first cell has a different formula from the others. You can use the same formula nesting another IF and referencing the row, so as to set one formula for the first row and one for the others.

share|improve this answer

edited Mar 23 at 21:43

answered Mar 23 at 17:19

Pythonista anonymousPythonista anonymous

1,52662658

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm using Teradata SQL. Interesting, I am not familiar with partition. Sounds like it could work. Also, if this can be done in regular excel with some sort of adjusted vlookup or index/match that works for me too.
  
  – biz_w
  Mar 23 at 21:19
  
  

  
  
  
  

add a comment | 

What SQL are you using? Are you familiar with CTE and partition?
Have a look here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/597b876e-eb00-4013-a613-97c377408668/rownumber-and-cte?forum=transactsql
and here: (answer and 2nd comment): Select the first instance of a record

You can use those ideas to create another field that tells you whether the row is the first, 2nd , 3rd etc occurrence of Col1. Eg you'd have something like
1 B Red 150
2 B Red 150

and you can then update col3 to be zero where this new field is not 1.

EDIT: since you asked about Excel: in Excel, sort by whatever criteria you may need (col 1 first, of course). Let's say that Col1 starts (excluding the heading) in cell C2. Set cell B2 =1. Then write this formula in cell B3:

=IF(C3=C2,B2+1,1)

and drag it all the way down. This will count the occurrences of col 1, ie it will tell you which is the first, 2nd etc time a given value appears in col1. You can then use it as as the basis to change the value in other columns.

Also, it is not good practice to have a column where the first cell has a different formula from the others. You can use the same formula nesting another IF and referencing the row, so as to set one formula for the first row and one for the others.

share|improve this answer

edited Mar 23 at 21:43

answered Mar 23 at 17:19

Pythonista anonymousPythonista anonymous

1,52662658

=IF(C3=C2,B2+1,1)

share|improve this answer

edited Mar 23 at 21:43

answered Mar 23 at 17:19

Pythonista anonymousPythonista anonymous

1,52662658

answered Mar 23 at 17:19

Pythonista anonymousPythonista anonymous

1,52662658

answered Mar 23 at 17:19

Pythonista anonymousPythonista anonymous

1,52662658