Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Merging With Same Cell Value and Matching Other Column Value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Merging With Same Cell Value and Matching Other Column Value

    Hello Experts,

    Following is actual data and the common column in both sheets is "Style No.".

    The two sheets have thousands of records. Lets say first sheet is with prices and order quantity and the second sheet is with actual shipped quantity.




    TABLE 1 WITH PRICES:
    Style No. Suppl. Style brand Color Sex M L XL Quantity/Pcs Unit price Amount
    14406146 MR-1 Mo light navy men 29 43 29 101 $ 2.50 $ 976.67
    14406146 MR-1 Mo black men 29 43 29 101 $ 2.50 $ 976.67
    14406146 MR-1 Mo dark beige men 29 43 29 101 $ 2.50 $ 976.67
    14406146 MR-1 Mo light olive men 29 43 29 101 $ 2.50 $ 976.67
    14406147 16203 Mo light navy men 29 43 29 101 $ 3.50 $ 1,139.28
    14406147 16203 Mo black men 29 43 29 101 $ 3.50 $ 1,139.28
    14406147 16203 Mo dark beige men 29 43 29 101 $ 3.50 $ 1,139.28
    14406147 16203 Mo light olive men 29 43 29 101 $ 3.50 $ 1,139.28
    14406148 3015 Mo navy men 29 43 29 101 $ 1.50 $ 1,374.61
    14406148 3015 Mo black men 29 43 29 101 $ 1.50 $ 1,374.61
    14406149 17323 Mo navy men 29 43 29 101 $ 2.30 $ 1,049.39
    14406149 17323 Mo black men 29 43 29 101 $ 2.30 $ 1,049.39
    14406150 19618 Mo black men 29 43 29 101 $ 2.21 $ 1,031.21
    14406150 19618 Mo navy men 29 43 29 101 $ 2.21 $ 1,031.21






    TABLE 2 WITH SHIPPED QUANTITIES:

    Carton Total Style No. Customs-No. Colour Lot Column1 Column2 Column3 Pcs/Ctn Total Pcs G.W. N.W. Carton Size Column4 Column5 CBM
    M L XL Length Width Height
    1 1 14406146 light navy M/L/XL 10 10 10 30 30 13 12.2 60 40 32 0.0768
    5 1 14406146 black M/L/XL 10 10 10 30 30 13 12.2 60 40 32 0.0768
    9 1 14406146 dark beige M/L/XL 10 10 10 30 30 13 12.2 60 40 32 0.0768
    13 1 14406146 light olive M/L/XL 10 10 10 30 30 13 12.2 60 40 32 0.0768
    1 1 14406147 light navy M/L/XL 10 10 10 30 30 14.4 13.2 60 40 32 0.0768
    5 1 14406147 black M/L/XL 10 10 10 30 30 14.4 13.2 60 40 32 0.0768
    9 1 14406147 dark beige M/L/XL 10 10 10 30 30 14.4 13.2 60 40 32 0.0768
    13 1 14406147 light olive M/L/XL 10 10 10 30 30 14.4 13.2 60 40 32 0.0768
    1 1 14406148 navy M/L/XL 10 10 10 30 30 19 18.1 60 40 50 0.12
    5 1 14406148 black M/L/XL 10 10 10 30 30 19 18.1 60 40 50 0.12
    1 1 14406149 navy M/L/XL 10 10 10 30 30 10.9 10.1 60 40 30 0.072
    5 1 14406149 black M/L/XL 10 10 10 30 30 10.9 10.1 60 40 30 0.072
    1 1 14406150 black M/L/XL 10 10 10 30 30 11 10.2 60 40 30 0.072
    5 1 14406150 navy M/L/XL 10 10 10 30 30 11 10.2 60 40 30 0.072













    Now what I want to achieve is:
    1) merge the style no. to a single row of same kind on second sheet (removing colour/lot columns)
    2) then relate/attach/bring the prices from the first sheet based on "Style No." right on the same row in front of it to calculate the value of actual shipped goods.

    I dont know if it can be acieved with PivotTable() or Vlookup() or Match(). Can you please guide / advise.

    I hope I am making sense.

    Thanks,

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Merging With Same Cell Value and Matching Other Column Value

    Hi shah0101,

    I'd normally build a third table, starting with the unique distinct list of Style Nos. then do the calculations. If you want it in Table 2 then I've inserted a column but the calculation only show for the last entry of a duplicated Style No.

    I don't see a Shipping Qty so I'm assuming the Carton number is the quantity shipped.

    This calculation assume that TABLE 1 prices are the same for duplicate Style Nos.

    The formula in C23 needs to be copied down all rows in TABLE 2.

    A B C D E F G H I J K
    1 TABLE 1 WITH PRICES:
    2 Style No. Suppl. Style brand Color Sex M L XL Quantity/Pcs Unit price Amount
    3 14406146 MR-1 Mo light navy men 29 43 29 101 $2.50 $976.67
    4 14406146 MR-1 Mo black men 29 43 29 101 $2.50 $976.67
    5 14406146 MR-1 Mo dark beige men 29 43 29 101 $2.50 $976.67
    6 14406146 MR-1 Mo light olive men 29 43 29 101 $2.50 $976.67
    7 14406147 16203 Mo light navy men 29 43 29 101 $3.50 $1,139.28
    8 14406147 16203 Mo black men 29 43 29 101 $3.50 $1,139.28
    9 14406147 16203 Mo dark beige men 29 43 29 101 $3.50 $1,139.28
    10 14406147 16203 Mo light olive men 29 43 29 101 $3.50 $1,139.28
    11 14406148 3015 Mo navy men 29 43 29 101 $1.50 $1,374.61
    12 14406148 3015 Mo black men 29 43 29 101 $1.50 $1,374.61
    13 14406149 17323 Mo navy men 29 43 29 101 $2.30 $1,049.39
    14 14406149 17323 Mo black men 29 43 29 101 $2.30 $1,049.39
    15 14406150 19618 Mo black men 29 43 29 101 $2.21 $1,031.21
    16 14406150 19618 Mo navy men 29 43 29 101 $2.21 $1,031.21
    17
    18
    19 TABLE 2 WITH SHIPPED QUANTITIES:
    20
    21 Carton Total Value Shipped Style No. Customs-No. Colour Lot Column1 Column2 Column3 Pcs/Ctn
    22 M L XL
    23 1 1 14406146 light navy M/L/XL 10 10 10 30
    24 5 1 14406146 black M/L/XL 10 10 10 30
    25 9 1 14406146 dark beige M/L/XL 10 10 10 30
    26 13 1 $70.00 14406146 light olive M/L/XL 10 10 10 30
    27 1 1 14406147 light navy M/L/XL 10 10 10 30
    28 5 1 14406147 black M/L/XL 10 10 10 30
    29 9 1 14406147 dark beige M/L/XL 10 10 10 30
    30 13 1 $98.00 14406147 light olive M/L/XL 10 10 10 30
    31 1 1 14406148 navy M/L/XL 10 10 10 30
    32 5 1 $9.00 14406148 black M/L/XL 10 10 10 30
    33 1 1 14406149 navy M/L/XL 10 10 10 30
    34 5 1 $13.80 14406149 black M/L/XL 10 10 10 30
    35 1 1 14406150 black M/L/XL 10 10 10 30
    36 5 1 $13.26 14406150 navy M/L/XL 10 10 10 30
    Sheet2 (2)

    Worksheet Formulas
    Cell Formula
    C23 =IF(COUNTIF($D23:$D$36,D23)=1,INDEX($J$3:$J$16,MATCH(D23,$A$3:$A$16,0))*SUMIFS($A$23:$A$36,$D$23:$D$36,D23),"")
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging With Same Cell Value and Matching Other Column Value

    Dear ToadStool,

    Thank you very much for your help.

    As you said: "I'd normally build a third table, starting with the unique distinct list of Style Nos. then do the calculations". I think this would be the right approach so I made a new sheet with Pivot and now all the "Style No." from table 2 are unique and have the relevant quantities (screenshot is attached, which is such a relief, thanks to you).

    Now is there any possibility that we can bring / fetch / link the relevant price, brand, etc of similar "Style No." from table 1 to the the pivot table or otherwise?

    Thanks in advance.

    -------
    P.S. for some reason I am unable to link the scrrenshot so here is the link: https://drive.google.com/file/d/1el5...ew?usp=sharing
    Last edited by shah0101; Sep 3rd, 2019 at 11:37 PM.

  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Merging With Same Cell Value and Matching Other Column Value

    It's too late here for me to build pivot tables so I'll just say that if you've the total then the INDEX MATCH will retrieve prices:

    Code:
    =INDEX($J$3:$J$16,MATCH(D23,$A$3:$A$16,0))
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging With Same Cell Value and Matching Other Column Value

    Quote Originally Posted by Toadstool View Post
    It's too late here for me to build pivot tables so I'll just say that if you've the total then the INDEX MATCH will retrieve prices:

    Code:
    =INDEX($J$3:$J$16,MATCH(D23,$A$3:$A$16,0))

    ===================

    I created the pivot table already with unique values based on "Style No.".

    The prices are in Sheet "Contract" Range L14:L200 - The range for "Style No." in this sheet is: A14:A200.

    The "Pivot" Sheet brought the records which are now shrinked to 56 records which is okay. The "Style No." column range is: A3:A58 -

    Can you please help me write the index code to bring the relevant prices in "Pivot" sheet in column range J3:J58 from the "Contract" sheet for matching "Style No.".

    Much appreciated!

    Thanks in advance.

  6. #6
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Merging With Same Cell Value and Matching Other Column Value

    Add a new column to TABLE 2 to get the unit price.


    A B C D
    21 Carton Total Style No. Price from TABLE 1
    22
    23 1 1 14406146 $2.50
    24 5 1 14406146 $2.50
    25 9 1 14406146 $2.50
    26 13 1 14406146 $2.50
    27 1 1 14406147 $3.50
    28 5 1 14406147 $3.50
    29 9 1 14406147 $3.50
    30 13 1 14406147 $3.50
    31 1 1 14406148 $1.50
    32 5 1 14406148 $1.50
    33 1 1 14406149 $2.30
    34 5 1 14406149 $2.30
    35 1 1 14406150 $2.21
    36 5 1 14406150 $2.21
    Sheet2 (3)

    Worksheet Formulas
    Cell Formula
    D23 =INDEX($J$3:$J$16,MATCH(C23,$A$3:$A$16,0))
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging With Same Cell Value and Matching Other Column Value

    Quote Originally Posted by Toadstool View Post
    Add a new column to TABLE 2 to get the unit price.


    A B C D
    21 Carton Total Style No. Price from TABLE 1
    22
    23 1 1 14406146 $2.50
    24 5 1 14406146 $2.50
    25 9 1 14406146 $2.50
    26 13 1 14406146 $2.50
    27 1 1 14406147 $3.50
    28 5 1 14406147 $3.50
    29 9 1 14406147 $3.50
    30 13 1 14406147 $3.50
    31 1 1 14406148 $1.50
    32 5 1 14406148 $1.50
    33 1 1 14406149 $2.30
    34 5 1 14406149 $2.30
    35 1 1 14406150 $2.21
    36 5 1 14406150 $2.21
    Sheet2 (3)

    Worksheet Formulas
    Cell Formula
    D23 =INDEX($J$3:$J$16,MATCH(C23,$A$3:$A$16,0))




    Dear Toadstool,

    Thank you soooooooooooooooooooo very much.

  8. #8
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Merging With Same Cell Value and Matching Other Column Value

    You're welcome!
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  9. #9
    New Member
    Join Date
    Jul 2019
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging With Same Cell Value and Matching Other Column Value

    Quote Originally Posted by Toadstool View Post
    You're welcome!

    Hi Toadstool,

    Need some more help.

    The scenario is still the same but now the prices are in three different worksheets. Can we now have a same index code which we can write for different ranges?

    TIA.

  10. #10
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Merging With Same Cell Value and Matching Other Column Value

    You can't have a single INDEX and/or MATCH using multiple ranges so you'll have to search each in turn.

    I've put tables 1,3 and 4 on one sheet just to demonstrate (so you'll need to add sheet names to the arrays) and I'm showing Style Nos and Price in different columns just for fun.

    The IFERROR searching tables 1 and 2 will fall into the next search if no match is found, otherwise it returns the first Pice. If the Style number in table 2 isn't on tables 1, 3 or 4 then you'll get a #N/A error.

    A B C D E F G H I J K L
    1 TABLE 1 WITH PRICES:
    2 Style No. Suppl. Style brand Color Sex M L XL Quantity/Pcs Unit price Amount
    3 14406146 MR-1 Mo light navy men 29 43 29 101 $2.50 $976.67
    4 2222222 MR-1 Mo black men 29 43 29 101 $4.22 $976.67
    5
    6 TABLE 3 WITH PRICES:
    7 Style No. Suppl. Style brand Color Sex Quantity/Pcs Unit price Amount
    8 14406147 16203 Mo light navy men 101 $1.55 $1,139.28
    9 14406147 16203 Mo black men 101 $1.55 $1,139.28
    10 14406147 16203 Mo dark beige men 101 $1.55 $1,139.28
    11
    12 TABLE 4 WITH PRICES:
    13 BD LD Style No. Suppl. Style brand Color Sex M L Quantity/Pcs Unit price Amount
    14 33 22 14406148 3015 Mo navy men 29 43 101 $3.33 $1,374.61
    15 22 33 14406148 3015 Mo black men 29 43 101 $3.33 $1,374.61
    16
    17 TABLE 2 WITH SHIPPED QUANTITIES:
    18
    19 Carton Total Value Shipped Style No. Extracted Price
    20
    21 1 1 14406146 2.5
    22 5 1 2222222 4.22
    23 9 1 14406147 1.55
    24 1 1 14406148 3.33
    25 5 1 3333333 #N/A
    Sheet1 (2)

    Worksheet Formulas
    Cell Formula
    E21 =IFERROR(INDEX($J$3:$J$4,MATCH(D21,$A$3:$A$4,0)),IFERROR(INDEX($H$8:$H$10,MATCH(D21,$B$8:$B$10,0)),INDEX($K$14:$K$15,MATCH(D21,$C$14:$C$15,0))))
    Life advice found on a book of matches: "Keep cool. Keep away from children."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •