Respective names required
Closed
raja
-
Sep 15, 2016 at 12:41 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Nov 4, 2016 at 08:33 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Nov 4, 2016 at 08:33 AM
Related:
- Respective names required
- Ideogram ai names - Guide
- My contacts list names - Guide
- Wow monk names - Guide
- Laptop keyboard symbol names - Guide
- How to count names in excel - Guide
6 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Sep 15, 2016 at 09:13 AM
Sep 15, 2016 at 09:13 AM
Ganesh, Good Morning.
The VLOOKUP Function was specially created for this case.
Sheet 1
Code........... --> A1:A100
Member name --> B1:B100
Sheet 2
Code --> A1:A50
Key.. --> B1:B50
Type1 -> C1:C50
Type2 -> D1:D50
Type3 -> E1:E50
Type4 -> F1:F50
Member name --> G1:G50 --> FORMULA
Try to use:
G1 --> =VLOOKUP(A1,'Sheet 1'!A1:B100,2,FALSE)
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
The VLOOKUP Function was specially created for this case.
Sheet 1
Code........... --> A1:A100
Member name --> B1:B100
Sheet 2
Code --> A1:A50
Key.. --> B1:B50
Type1 -> C1:C50
Type2 -> D1:D50
Type3 -> E1:E50
Type4 -> F1:F50
Member name --> G1:G50 --> FORMULA
Try to use:
G1 --> =VLOOKUP(A1,'Sheet 1'!A1:B100,2,FALSE)
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Hello Belo Horizonte, Brasil.
Marcílio Lobão,
Thank you sir.. the problem is resolved..
One more doubt...?
Column 1 column 2
BM 4.30
CM 5.30
BM 6.03
BM 4.50
CM 9.10
.
.
.
BM 2.81
My Question : how to calculate the BM values sum only?
Give me formula ?
Kind Regards,
Ganesh
Marcílio Lobão,
Thank you sir.. the problem is resolved..
One more doubt...?
Column 1 column 2
BM 4.30
CM 5.30
BM 6.03
BM 4.50
CM 9.10
.
.
.
BM 2.81
My Question : how to calculate the BM values sum only?
Give me formula ?
Kind Regards,
Ganesh
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Sep 15, 2016 at 10:53 AM
Sep 15, 2016 at 10:53 AM
Ganesh, Good Morning.
The SUMIF Function will work for you.
Code --> A1:A100
Value --> B1:B100
Try to use:
D1 --> =SUMIF(A1:A100,"BM",B1:B100)
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
The SUMIF Function will work for you.
Code --> A1:A100
Value --> B1:B100
Try to use:
D1 --> =SUMIF(A1:A100,"BM",B1:B100)
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Hello Sir,
This is Ganesh. .
I have one more doubt..?
Please click below link.
https://www.dropbox.com/s/1j2ieeyc5jxqq28/Sample.xlsx?dl=0
How i have two columns fat,snf one sheet(1001-bm) . i have another sheet(buffalo) that contains fat and snf values i want give lookup or meet the coincide value.
So please give a formula .
Thanks,
Ganesh
This is Ganesh. .
I have one more doubt..?
Please click below link.
https://www.dropbox.com/s/1j2ieeyc5jxqq28/Sample.xlsx?dl=0
How i have two columns fat,snf one sheet(1001-bm) . i have another sheet(buffalo) that contains fat and snf values i want give lookup or meet the coincide value.
So please give a formula .
Thanks,
Ganesh
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Oct 30, 2016 at 11:59 AM
Oct 30, 2016 at 11:59 AM
Ganesh, Good Morning.
The VLOOKUP Function will be useful for you in this case.
Your necessity is not clear.
Do you want a Exact Cross Reference data using Colunm B for FAT and Line 2 for SNF ?
How did you find a 45,6 result presented in your example?
I think the exact result must be 43,8.
For you, what is the result for FAT=5,40 and SNF=8,12 ?
Please, tell us how you find the desired result?
The VLOOKUP Function will be useful for you in this case.
Your necessity is not clear.
Do you want a Exact Cross Reference data using Colunm B for FAT and Line 2 for SNF ?
How did you find a 45,6 result presented in your example?
I think the exact result must be 43,8.
For you, what is the result for FAT=5,40 and SNF=8,12 ?
Please, tell us how you find the desired result?
Hello Sir,
Thank you for prompt reply.
as you said, I want a Exact Cross Reference data using Column B for FAT and Line 2 for SNF ?
Yes. as per my example,the result is 43.80
Also i want the results like as below
For example
FAT SNF Value
5.00 7.40 27.80
5.00 7.41 27.82
5.00 7.42 27.84
.
.
.
5.00 7.50 28.00
I Mean if we incease SNF value to 0.01,it automatically the rate 0.02 increased
So please give me vlookup function formula .
.
Thanks
Ganesh
Thank you for prompt reply.
as you said, I want a Exact Cross Reference data using Column B for FAT and Line 2 for SNF ?
Yes. as per my example,the result is 43.80
Also i want the results like as below
For example
FAT SNF Value
5.00 7.40 27.80
5.00 7.41 27.82
5.00 7.42 27.84
.
.
.
5.00 7.50 28.00
I Mean if we incease SNF value to 0.01,it automatically the rate 0.02 increased
So please give me vlookup function formula .
.
Thanks
Ganesh
Hello Mazzaropi,
as you said, I want a Exact Cross Reference data using Column B for FAT and Line 2 for SNF ?
Yes. as per my example,the result is 43.80
Also i want the results like as below
For example
FAT SNF Value
5.00 7.40 27.80
5.00 7.41 27.82
5.00 7.42 27.84
.
.
.
5.00 7.50 28.00
I Mean if we incease SNF value to 0.01,it automatically the rate 0.02 increased
So please give me vlookup function formula .
.
Thanks
Ganesh
as you said, I want a Exact Cross Reference data using Column B for FAT and Line 2 for SNF ?
Yes. as per my example,the result is 43.80
Also i want the results like as below
For example
FAT SNF Value
5.00 7.40 27.80
5.00 7.41 27.82
5.00 7.42 27.84
.
.
.
5.00 7.50 28.00
I Mean if we incease SNF value to 0.01,it automatically the rate 0.02 increased
So please give me vlookup function formula .
.
Thanks
Ganesh
Didn't find the answer you are looking for?
Ask a question
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Nov 3, 2016 at 02:24 PM
Nov 3, 2016 at 02:24 PM
Ganesh, Good evening.
We asked you if you want a Exact Cross Reference data using Colunm B for FAT and Line 2 for SNF.
And you said: YES.
But reading your new explanation it's not corret.
You need a more complex search than a simple Cross Reference.
I'm not sure if I understood well your necessity.
Take a look at this example that a included a formula for you.
https://www.sendspace.com/file/xqiauj
Do many tests to certify the formula is giving you a CORRECT result.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
We asked you if you want a Exact Cross Reference data using Colunm B for FAT and Line 2 for SNF.
And you said: YES.
But reading your new explanation it's not corret.
You need a more complex search than a simple Cross Reference.
I'm not sure if I understood well your necessity.
Take a look at this example that a included a formula for you.
https://www.sendspace.com/file/xqiauj
Do many tests to certify the formula is giving you a CORRECT result.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Hello there,
first of all, thank you so much for your help.I ever grateful to you.
I have one more error in my file..please click on the below link.
when i want to transfer data from Main database to other sheets, the values in the columns "Rate/kg & amount" is changed to 01.4,0.00,0.12..etc
So Can you look at this file & give me solution.
Also i want to maintain a every day data in a separate excel sheet. Can you please give me an idea & solution.
https://www.dropbox.com/s/ybdjtqu7o74f0ac/sample%202.xlsm?dl=0
Thanks ,
Your's
Ganesh
first of all, thank you so much for your help.I ever grateful to you.
I have one more error in my file..please click on the below link.
when i want to transfer data from Main database to other sheets, the values in the columns "Rate/kg & amount" is changed to 01.4,0.00,0.12..etc
So Can you look at this file & give me solution.
Also i want to maintain a every day data in a separate excel sheet. Can you please give me an idea & solution.
https://www.dropbox.com/s/ybdjtqu7o74f0ac/sample%202.xlsm?dl=0
Thanks ,
Your's
Ganesh
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Nov 4, 2016 at 08:33 AM
Nov 4, 2016 at 08:33 AM
Ganesh, Good morning.
I don't know Excel VBA programming.
"...the values in the columns "Rate/kg & amount" is changed to 01.4, 0.00, 0.12 ..etc ..."
When your macro runs it is erasing part of the data of the two tabs: Buffalo and Cow.
So the search is all wrong.
The problem is in the Macro.
In tab Buffalo, column B line 18 to 53, the values have several decimal places.
As the cell is formatted to show only two decimal places you can see 6.50 but actually is 6.49999999999999.
This will make the search fails when you search this value.
I advise you to correct these numbers to prevent future problems.
"...Also i want to maintain a every day data in a separate excel sheet. Can you please give me an idea & solution. ..."
It may be helpful for you to have the spreadsheet with daily data in a file and another file with data separated by codes.
Thus, using VBA you can update them whenever necessary.
As we do not know the logistics of your business it is difficult to speak with precision the best solution.
Checking more carefully your spreadsheet, we have noticed that you use different formulas for each type of milk.
Do you enter the formulas manually for each type of milk found?
If the answer is YES, this is not necessary.
It is hard work and time spent typing.
Make Excel work for you.
The automation serves this purpose.
Besides being much faster and much more secure and professional.
Try to use:
Main Database --> N6
=IF(F6="BM",INDEX(Buffalo!$C$3:$X$53,MATCH(J6,Buffalo!$B$3:$B$53,1),MATCH(K6,Buffalo!$C$2:$X$2,1)),IF(F6="CM",INDEX(Cow!$C$3:$W$28,MATCH(J6,Cow!$B$3:$B$28,1),MATCH(K6,Cow!$C$2:$W$2,1)),"")) + MOD(K6,0.1)*2
Copy it down as necessary.
I hope it helps.
Have a nice weekend.
--
Belo Horizonte, Brasil.
Marcílio Lobão
I don't know Excel VBA programming.
"...the values in the columns "Rate/kg & amount" is changed to 01.4, 0.00, 0.12 ..etc ..."
When your macro runs it is erasing part of the data of the two tabs: Buffalo and Cow.
So the search is all wrong.
The problem is in the Macro.
In tab Buffalo, column B line 18 to 53, the values have several decimal places.
As the cell is formatted to show only two decimal places you can see 6.50 but actually is 6.49999999999999.
This will make the search fails when you search this value.
I advise you to correct these numbers to prevent future problems.
"...Also i want to maintain a every day data in a separate excel sheet. Can you please give me an idea & solution. ..."
It may be helpful for you to have the spreadsheet with daily data in a file and another file with data separated by codes.
Thus, using VBA you can update them whenever necessary.
As we do not know the logistics of your business it is difficult to speak with precision the best solution.
Checking more carefully your spreadsheet, we have noticed that you use different formulas for each type of milk.
Do you enter the formulas manually for each type of milk found?
If the answer is YES, this is not necessary.
It is hard work and time spent typing.
Make Excel work for you.
The automation serves this purpose.
Besides being much faster and much more secure and professional.
Try to use:
Main Database --> N6
=IF(F6="BM",INDEX(Buffalo!$C$3:$X$53,MATCH(J6,Buffalo!$B$3:$B$53,1),MATCH(K6,Buffalo!$C$2:$X$2,1)),IF(F6="CM",INDEX(Cow!$C$3:$W$28,MATCH(J6,Cow!$B$3:$B$28,1),MATCH(K6,Cow!$C$2:$W$2,1)),"")) + MOD(K6,0.1)*2
Copy it down as necessary.
I hope it helps.
Have a nice weekend.
--
Belo Horizonte, Brasil.
Marcílio Lobão
