find text match amount and paste from sheet1 to sheet2

[visha_1984]

Hi Team,

I have tried vlookup for col "F" but i didnot get output which i want.
here we go what i want
1) Copy party name from col "E" sheet1 and find in col "F" sheet2 ,after that
2) match amount in col "D" sheet1 with col "L" sheet2 and paste CODE from sheet1 to col "M" in sheet2
3) If amount not match leave blank or AMOUNT NOT MATCH ,then repeat step - 1,2
4) If party name not found in sheet2 try next party.

I also tried pivot and vlookup but amount could not matched,Is there any formula or VBA code to do this
I have attached sample workbook.

Any help would be much appreciated.
Visha

[RobertMika]

Please see attached

Find%20-paste(1).xlsx

Sheet 1 G2:
=SUMIF(Sheet2!$F$2:$F$15,E2,Sheet2!$L$2:$L$15)
Sheet2 array formula(Confirm C+S+E)

=IFERROR(INDEX(Sheet1!$F$2:$F$6,MATCH(F2&SUMIF($F$2:$F$15,F2,$L$2:$L$15),Sheet1!$E$2:$E$6&Sheet1!$D$2:$D$6,0)),"no match")

Helper in Sheet1.
This can be done with one formula but this way is simpler and will take less resources.

[visha_1984]

Hi RobertMika,

Thank for help formula work perfectly
just 1 Question if in sheet1 there is party name 2 Time and amount also match then what should i do.
i have attached my query.

visha

[RobertMika]

The formual should point to column G not D
=IFERROR(INDEX(Sheet1!$F$2:$F$7,MATCH(F2&SUMIF($F$2:$F$15,F2,$L$2:$L$15),Sheet1!$E$2:$E$7&Sheet1!$G$2:$G$7,0)),"no match")

Is this what you after?

[visha_1984]

The formual should point to column G not D
=IFERROR(INDEX(Sheet1!$F$2:$F$7,MATCH(F2&SUMIF($F$2:$F$15,F2,$L$2:$L$15),Sheet1!$E$2:$E$7&Sheet1!$G$2:$G$7,0)),"no match")

Is this what you after?

Hi RobertMika,

No,i am trying to match amount with Name sheet1 to sheet2

in sheet1
AMOUNT NAME
140 AGRAWAL AGEN
5 AGRAWAL AGEN


in sheet2
Name AMOUNT
AGRAWAL AGEN 35
AGRAWAL AGEN 25
AGRAWAL AGEN 35
AGRAWAL AGEN 50

both amount should match with your array formula.

I have attached new workbook slight change is there in sheet1 col "G" ,if it help you pls change formula,
i appreciate your help

[RobertMika]

Does not they do?
Is column G your desired outocme?
Or is colmn M on SHeet 2 your desired outocme?
Is the any reason you have left G3 empty?

[visha_1984]

on sheet2 col "M" is my desired outcome not G
and No there is no specific reason for G3
If i fill G3 with amount of 145 then then your formula work for me ??

[RobertMika]

I got it now.
You have got two codes with one Name
(AGRAWAL AGEN)
Hmm..
You can either have any of the code, the first or the last
Or we need to add another criteria but I can not see anyhig which match column in Sheet 1 to Sheet2

[visha_1984]

Hey Robert

I am trying to match amount not column.

[RobertMika]

My first formula has worked as you you have only one AGRAWAL AGEN
and one assigned code.
Now if you got two codes for AGRAWAL AGEN we have got
140 for code ../..09
and 5 for code ../..10
My qustion is:
Does this amount match or not?
Are we based purely on Name or the Code is necessary.
BTW on Sheet 2 your SO.No is in the same format as your code on SHeet1
is this importan in your calculations?

[visha_1984]

If formula get one code for 140 and 5 ,there in no problem but amount should match.

[RobertMika]

The amount for AGRAWAL AGEN Sheet 2 is 35+25+35+50=145
On sheet1 AGRAWAL AGEN 140+5=145.
The code in column M =MAG/Distributor/4013381//PR/12-2013-14/000309

What am I missing here?

[visha_1984]

Correct.
in sheet1 col "G" sum of amount is for sheet1 not for sheet2

[RobertMika]

So that all correct?
I'm loosing track here.
If the code does not matter than your calcuation shoudl be correct.

[visha_1984]

if i use thread #2 formula ,showing amount not match for AGRAWAL AGEN

[RobertMika]

If you match Column D (single amount)on Sheet1 it will not match but if you match column G(sum per Name) It will.
(the formula is post #2 shoudl have been:
=IFERROR(INDEX(Sheet1!$F$2:$F$7,MATCH(F2&SUMIF($F$2:$F$15,F2,$L$2:$L$15),Sheet1!$E$2:$E$7&Sheet1!$G$2:$G$7,0)),"no match")

[visha_1984]

I have check formula work fine,but some of them showing not match because of this 11293.88 in cell amount showing 11294 but in formula bar showing 11293.88 is there any solution for this i have try format cell also. pls help

[RobertMika]

I have check formula work fine,but some of them showing not match because of this 11293.88 in cell amount showing 11294 but in formula bar showing 11293.88 is there any solution for this i have try format cell also. pls help

I can not see this amount on your spreadsheet.

[visha_1984]

Hi,
check this attachment L10 in formual bar.

[RobertMika]

=IFERROR(INDEX(Sheet1!$F$2:$F$7,MATCH(F2&ROUND(SUMIF($F$2:$F$15,F2,$L$2:$L$15),0),Sheet1!$E$2:$E$7&Sheet1!$G$2:$G$7,0)),"no match")
Confirm Control+Shift+Enter

[visha_1984]

Fantastic RobertMika

Thank u very much have a Good Day...

[RobertMika]

You are welcome.