Add only Additional Statement at end

S

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
884
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Team,
Hope you are doing well:)

I need 1 help on below data.
Need to add additional statement in J (Attribute 2020) column at end based on column B (Attribute 2021) extra statement.
Column F and J statement order will be same only need to add new data based on Column B

Fs.xlsx
ABCDEFGHIJKL
12021 RankAttribute 20212021 Differentiation2020 RankAttribute 20202020 DifferentiationAttribute 202013
21I would recommend this retailer to othersAmazon, Dollarama11I would recommend this retailer to othersAmazon, DollaramaTRUEI would recommend this retailer to others1Amazon, Dollarama
32I love this retailerAmazon, Costco, Dollarama22I love this retailerAmazon, Costco, DollaramaTRUEI love this retailer2Amazon, Costco, Dollarama
43I trust this retailer33I trust this retailerTRUEI trust this retailer30.0
55This retailer understands my needs44This retailer understands my needsTRUEThis retailer understands my needs50.0
68Helps simplify my lifeAmazon, Dollarama75Provides good customer serviceTRUEProvides good customer service19Amazon
712Is worth my time to make the trip to this storeCostco, Dollarama#N/A6Offers a good selection of products that meets my needsTRUEOffers a good selection of products that meets my needs370.0
816This retailer cares about my health and well beingPharmaprix/SDM#N/A7Helps simplify my lifeAmazon, DollaramaTRUEHelps simplify my life8Amazon, Dollarama
919Provides good customer serviceAmazon58Offers good value for the moneyAmazon, Costco, Dollar Tree, Dollarama, No Frills, WalmartTRUEOffers good value for the money40Dollarama, No Frills, Walmart
1037Offers a good selection of products that meets my needs69Provides a pleasant atmosphere for shoppingPharmaprix/SDMTRUEProvides a pleasant atmosphere for shopping43Canadian Tire, No Frills, Pharmaprix/SDM, RCSS, Total Loblaws
1138Helps simplify my lifeAmazon, Pharmaprix/SDM710This retailer is fun to shopAmazon, Costco, Dollar Tree, DollaramaTRUEThis retailer is fun to shop9Amazon, Costco, Dollarama
1240Offers good value for the moneyDollarama, No Frills, Walmart8Adding 2021Is worth my time to make the trip to this store12Costco, Dollarama
1343Provides a pleasant atmosphere for shoppingCanadian Tire, No Frills, Pharmaprix/SDM, RCSS, Total Loblaws9Adding 2021This retailer cares about my health and well being16Pharmaprix/SDM
149This retailer is fun to shopAmazon, Costco, Dollarama10Adding 2021This retailer has a positive impact on my local community42Canadian Tire
1542This retailer has a positive impact on my local communityCanadian Tire#N/A
16
17
Sheet1 (2)
Cell Formulas
RangeFormula
K2:L11K2=INDEX($A:$C,MATCH($J2,$B:$B,0),K$1)
I2:I11I2=F2=J2
D2:D14D2=MATCH($B2,$F$2:$F$11,0)



Please do the need full.

Thanks
Sanjeev
 
Sort by date Sort by votes
petertenthije said:
It is still not exactly clear what you are looking for.

So let me get this clear:
columns A to C are reviews for 2021
Columns E to G are reviews for 2020

in column J you want every unique review for 2020 and 2021 combined?
Click to expand...
perfect Peterten :)

Same thing i was looking for.

Thank you for explaining
Thanks.
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You can use this macro.

Please be sure to test this first on a copy of your spreadsheet!

VBA Code: 
Sub Macro1()

' Remove existing data from column J
    Range("J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
   
' Copy contents of column F
    Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

' Paste data into column J
    Range("J2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

' Copy data from column B
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

' Paste data into column J
    Range("J1").Select
    Selection.End(xlDown).Select
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

' Remove duplicates
    ActiveSheet.Range("$J:$J").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub
 
Upvote 0
petertenthije said:
You can use this macro.

Please be sure to test this first on a copy of your spreadsheet!

VBA Code: 
Sub Macro1()

' Remove existing data from column J
    Range("J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
  
' Copy contents of column F
    Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

' Paste data into column J
    Range("J2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

' Copy data from column B
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

' Paste data into column J
    Range("J1").Select
    Selection.End(xlDown).Select
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

' Remove duplicates
    ActiveSheet.Range("$J:$J").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub
Click to expand...
Thanks so much for your time on this.

I have run the macro and work well with data pulling

Just to check if it is possible to get first "F" order statement as it is then rest unique statement from "B "column (B column is 2021 data)

Thank in Advance:)
Sanjeev
 
Upvote 0
petertenthije said:
What do you mean? That the order of column F is used, and then the new reviews of column B are added? Because the macro is already doing that.
Click to expand...
Hi Peter,

Like i need same order available in column "F" and unique statement from "B" column (in J column).

Regards,
Sanjeev.
 
Upvote 0
Hi Team,

I have help in tweaking the below data.
I am able to run the macro but getting blank in M46 (I don't want any blank space between statements) and i want starting point from M5

Herr is the macro..
"
Sub Macro1()

' Remove existing data from column J
Range("M5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

' Copy contents of column F
Range("H5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

' Paste data into column J
Range("M5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' Copy data from column B
Range("C5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

' Paste data into column J
Range("M5").Select
Selection.End(xlDown).Select
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' Remove duplicates
ActiveSheet.Range("$M:$M").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub


"


Here is the Data

book1
ABCDEFGHIJKLM
1[Output.xlsx_updated1.xlsx][CA RES -with JFM21-Drivers.Differentiations.xlsx]
2Total Retailer-NationalTotal Store-National-P6M
3ABCABCI would recommend this retailer to others
412020 RankAttribute2020 Differentiation2020 RankAttribute2020 DifferentiationI love this retailer
521I would recommend this retailer to othersAmazon, Dollarama1I would recommend this retailer to othersAmazon, DollaramaI trust this retailer
632I love this retailerAmazon, Costco, Dollarama2I love this retailerAmazon, Costco, DollaramaThis retailer understands my needs
743I trust this retailer3I trust this retailerProvides good customer service
855This retailer understands my needs4This retailer understands my needsOffers a good selection of products that meets my needs
968Helps simplify my lifeAmazon, Dollarama5Provides good customer serviceHelps simplify my life
10712Is worth my time to make the trip to this storeCostco, Dollarama6Offers a good selection of products that meets my needsOffers good value for the money
11816This retailer cares about my health and well beingPharmaprix/SDM7Helps simplify my lifeAmazon, DollaramaProvides a pleasant atmosphere for shopping
12919My personal information is secure when I shop this retailerAmazon8Offers good value for the moneyAmazon, Costco, Dollar Tree, Dollarama, No Frills, WalmartThis retailer is fun to shop
131037This retailer is taking steps to protect the environment9Provides a pleasant atmosphere for shoppingPharmaprix/SDMIs worth my time to make the trip to this store
141138Provides personalized offers and recommendationsAmazon, Pharmaprix/SDM10This retailer is fun to shopAmazon, Costco, Dollar Tree, DollaramaEmployees are friendly and helpful
151242This retailer has a positive impact on my local communityCanadian Tire11Is worth my time to make the trip to this storeCostco, Dollarama, No FrillsShopping this retailer saves me time
16134Provides good customer service12Employees are friendly and helpfulPharmaprix/SDMThe products I want are consistently in stock
17146Provides a pleasant atmosphere for shoppingPharmaprix/SDM13Shopping this retailer saves me timeAmazonThe layout of this store makes it easy to shop
18159This retailer is fun to shopAmazon, Costco, Dollarama14The products I want are consistently in stockIn-store displays make shopping easier
191610Employees are friendly and helpfulHome Depot, Pharmaprix/SDM15The layout of this store makes it easy to shopDollaramaMakes it easy to buy products I have previously purchased
201726Inspires me to explore and try new productsAmazon, Costco16In-store displays make shopping easierIGA/IGA ExtraThe store shelves are organized and easy to shop
211833Returning or exchanging items is quick and easyCanadian Tire, Costco, Home Depot, Walmart17Makes it easy to buy products I have previously purchasedAmazonOffers a good selection of products across price points
221934This retailer provides a safe and hygienic environment for employees and shoppers18The store shelves are organized and easy to shopDollarama, Pharmaprix/SDMHas good sales/promotions
23207Offers a good selection of products that meets my needsAmazon19Offers a good selection of products across price pointsAmazon, WalmartOffers a good selection of the latest, newest products
242125Offers high quality own-label brandsCostco20Has good sales/promotionsCanadian Tire, No Frills, WalmartProducts on promotion are consistently in stock
252228Offers a good selection of the latest, newest productsAmazon21Offers a good selection of the latest, newest productsAmazonHas all the items I want in one place for one-stop shopping
262329Offers a good selection of name brand productsAmazon22Products on promotion are consistently in stockCheckout is quick and easy in the store
272431Offers a good selection of items that I can only find at this retailerAmazon, Costco23Has all the items I want in one place for one-stop shoppingAmazon, RCSS, WalmartInspires me to explore and try new products
282536Offers a good selection of natural productsRCSS, Total Loblaws24Checkout is quick and easy in the storeDollaramaMy personal information is secure when I shop this retailer
292641Offers a good selection of seasonal items throughout the yearAmazon, Canadian Tire, Walmart25Inspires me to explore and try new productsAmazon, CostcoOffers high quality own-label brands
302713The products I want are consistently in stock26My personal information is secure when I shop this retailerAmazonIs easy to get in and out quickly
312814The layout of this store makes it easy to shopDollarama, Pharmaprix/SDM27Offers high quality own-label brandsCostco, Total LoblawsReturning or exchanging items is quick and easy
322915The store shelves are organized and easy to shopDollarama, Pharmaprix/SDM28Is easy to get in and out quicklyDollarama, Pharmaprix/SDMProvides the information I need at shelf to help me select the right products
333017Shopping this retailer saves me timeAmazon29Returning or exchanging items is quick and easyCanadian Tire, Costco, Home Depot, WalmartOffers a good selection of name brand products
343118In-store displays make shopping easierWalmart30Provides the information I need at shelf to help me select the right productsThis retailer cares about my health and well being
353222Checkout is quick and easy in the storeDollarama, Pharmaprix/SDM31Offers a good selection of name brand productsMetro/MetroPlusOffers a good selection of items that I can only find at this retailer
363323Products on promotion are consistently in stock32This retailer cares about my health and well beingPharmaprix/SDMMembership is worth it
373424Makes it easy to buy products I have previously purchasedAmazon33Offers a good selection of items that I can only find at this retailerAmazon, CostcoProvides personalized offers and recommendations
383527Is easy to get in and out quicklyDollarama, Pharmaprix/SDM34Membership is worth itAmazon, CostcoProvides low prices, even if there's no promotion
393630Provides the information I need at shelf to help me select the right products35Provides personalized offers and recommendationsAmazon, Pharmaprix/SDMOffers a good selection of seasonal items throughout the year
403732Has all the items I want in one place for one-stop shoppingAmazon, RCSS, Walmart36Provides low prices, even if there's no promotionCostco, Dollar Tree, Dollarama, No Frills, WalmartOffers a good selection of natural products
413839Retailer uses technology to improve my shopping experience (app, scanners, digital displays, etc.)Amazon, Walmart37Offers a good selection of seasonal items throughout the yearCanadian Tire, WalmartRetailer uses technology to improve my shopping experience (app, scanners, digital displays, etc.)
423944Circulars/features/emails help me determine what to buyCanadian Tire, Metro/MetroPlus38Offers a good selection of natural productsRCSS, Sobeys, Total LoblawsOffers coupons on items that I want
434046Is conveniently located near meDollarama, Pharmaprix/SDM39Retailer uses technology to improve my shopping experience (app, scanners, digital displays, etc.)WalmartLoyalty/Rewards program rewards me for shopping this retailer
444111Offers good value for the moneyAmazon, Dollarama, No Frills, Walmart40Offers coupons on items that I wantRCSSCirculars/features/emails help me determine what to buy
454220Offers a good selection of products across price pointsAmazon, Walmart41Loyalty/Rewards program rewards me for shopping this retailerMetro/MetroPlus, No Frills, Pharmaprix/SDM, RCSS, Total LoblawsIs conveniently located near me
464321Has good sales/promotionsCanadian Tire, No Frills, Walmart42Circulars/features/emails help me determine what to buyCanadian Tire, IGA/IGA Extra, Metro/MetroPlus, No Frills, Pharmaprix/SDM
474435Membership is worth itAmazon, Costco43Is conveniently located near meDollarama, Pharmaprix/SDMThis retailer is taking steps to protect the environment
484540Provides low prices, even if there's no promotionDollarama, No Frills, WalmartThis retailer has a positive impact on my local community
494643Loyalty/Rewards program rewards me for shopping this retailerCanadian Tire, No Frills, Pharmaprix/SDM, RCSS, Total LoblawsThis retailer provides a safe and hygienic environment for employees and shoppers
504745Offers coupons on items that I want
5148
Sheet1
 
Upvote 0
sksanjeev786 said:
Hi Team,

I have help in tweaking the below data.
I am able to run the macro but getting blank in M46 (I don't want any blank space between statements) and i want starting point from M5

Herr is the macro..
"
Sub Macro1()

' Remove existing data from column J
Range("M5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

' Copy contents of column F
Range("H5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

' Paste data into column J
Range("M5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' Copy data from column B
Range("C5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

' Paste data into column J
Range("M5").Select
Selection.End(xlDown).Select
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' Remove duplicates
ActiveSheet.Range("$M:$M").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub


"


Here is the Data

book1
ABCDEFGHIJKLM
1[Output.xlsx_updated1.xlsx][CA RES -with JFM21-Drivers.Differentiations.xlsx]
2Total Retailer-NationalTotal Store-National-P6M
3ABCABCI would recommend this retailer to others
412020 RankAttribute2020 Differentiation2020 RankAttribute2020 DifferentiationI love this retailer
521I would recommend this retailer to othersAmazon, Dollarama1I would recommend this retailer to othersAmazon, DollaramaI trust this retailer
632I love this retailerAmazon, Costco, Dollarama2I love this retailerAmazon, Costco, DollaramaThis retailer understands my needs
743I trust this retailer3I trust this retailerProvides good customer service
855This retailer understands my needs4This retailer understands my needsOffers a good selection of products that meets my needs
968Helps simplify my lifeAmazon, Dollarama5Provides good customer serviceHelps simplify my life
10712Is worth my time to make the trip to this storeCostco, Dollarama6Offers a good selection of products that meets my needsOffers good value for the money
11816This retailer cares about my health and well beingPharmaprix/SDM7Helps simplify my lifeAmazon, DollaramaProvides a pleasant atmosphere for shopping
12919My personal information is secure when I shop this retailerAmazon8Offers good value for the moneyAmazon, Costco, Dollar Tree, Dollarama, No Frills, WalmartThis retailer is fun to shop
131037This retailer is taking steps to protect the environment9Provides a pleasant atmosphere for shoppingPharmaprix/SDMIs worth my time to make the trip to this store
141138Provides personalized offers and recommendationsAmazon, Pharmaprix/SDM10This retailer is fun to shopAmazon, Costco, Dollar Tree, DollaramaEmployees are friendly and helpful
151242This retailer has a positive impact on my local communityCanadian Tire11Is worth my time to make the trip to this storeCostco, Dollarama, No FrillsShopping this retailer saves me time
16134Provides good customer service12Employees are friendly and helpfulPharmaprix/SDMThe products I want are consistently in stock
17146Provides a pleasant atmosphere for shoppingPharmaprix/SDM13Shopping this retailer saves me timeAmazonThe layout of this store makes it easy to shop
18159This retailer is fun to shopAmazon, Costco, Dollarama14The products I want are consistently in stockIn-store displays make shopping easier
191610Employees are friendly and helpfulHome Depot, Pharmaprix/SDM15The layout of this store makes it easy to shopDollaramaMakes it easy to buy products I have previously purchased
201726Inspires me to explore and try new productsAmazon, Costco16In-store displays make shopping easierIGA/IGA ExtraThe store shelves are organized and easy to shop
211833Returning or exchanging items is quick and easyCanadian Tire, Costco, Home Depot, Walmart17Makes it easy to buy products I have previously purchasedAmazonOffers a good selection of products across price points
221934This retailer provides a safe and hygienic environment for employees and shoppers18The store shelves are organized and easy to shopDollarama, Pharmaprix/SDMHas good sales/promotions
23207Offers a good selection of products that meets my needsAmazon19Offers a good selection of products across price pointsAmazon, WalmartOffers a good selection of the latest, newest products
242125Offers high quality own-label brandsCostco20Has good sales/promotionsCanadian Tire, No Frills, WalmartProducts on promotion are consistently in stock
252228Offers a good selection of the latest, newest productsAmazon21Offers a good selection of the latest, newest productsAmazonHas all the items I want in one place for one-stop shopping
262329Offers a good selection of name brand productsAmazon22Products on promotion are consistently in stockCheckout is quick and easy in the store
272431Offers a good selection of items that I can only find at this retailerAmazon, Costco23Has all the items I want in one place for one-stop shoppingAmazon, RCSS, WalmartInspires me to explore and try new products
282536Offers a good selection of natural productsRCSS, Total Loblaws24Checkout is quick and easy in the storeDollaramaMy personal information is secure when I shop this retailer
292641Offers a good selection of seasonal items throughout the yearAmazon, Canadian Tire, Walmart25Inspires me to explore and try new productsAmazon, CostcoOffers high quality own-label brands
302713The products I want are consistently in stock26My personal information is secure when I shop this retailerAmazonIs easy to get in and out quickly
312814The layout of this store makes it easy to shopDollarama, Pharmaprix/SDM27Offers high quality own-label brandsCostco, Total LoblawsReturning or exchanging items is quick and easy
322915The store shelves are organized and easy to shopDollarama, Pharmaprix/SDM28Is easy to get in and out quicklyDollarama, Pharmaprix/SDMProvides the information I need at shelf to help me select the right products
333017Shopping this retailer saves me timeAmazon29Returning or exchanging items is quick and easyCanadian Tire, Costco, Home Depot, WalmartOffers a good selection of name brand products
343118In-store displays make shopping easierWalmart30Provides the information I need at shelf to help me select the right productsThis retailer cares about my health and well being
353222Checkout is quick and easy in the storeDollarama, Pharmaprix/SDM31Offers a good selection of name brand productsMetro/MetroPlusOffers a good selection of items that I can only find at this retailer
363323Products on promotion are consistently in stock32This retailer cares about my health and well beingPharmaprix/SDMMembership is worth it
373424Makes it easy to buy products I have previously purchasedAmazon33Offers a good selection of items that I can only find at this retailerAmazon, CostcoProvides personalized offers and recommendations
383527Is easy to get in and out quicklyDollarama, Pharmaprix/SDM34Membership is worth itAmazon, CostcoProvides low prices, even if there's no promotion
393630Provides the information I need at shelf to help me select the right products35Provides personalized offers and recommendationsAmazon, Pharmaprix/SDMOffers a good selection of seasonal items throughout the year
403732Has all the items I want in one place for one-stop shoppingAmazon, RCSS, Walmart36Provides low prices, even if there's no promotionCostco, Dollar Tree, Dollarama, No Frills, WalmartOffers a good selection of natural products
413839Retailer uses technology to improve my shopping experience (app, scanners, digital displays, etc.)Amazon, Walmart37Offers a good selection of seasonal items throughout the yearCanadian Tire, WalmartRetailer uses technology to improve my shopping experience (app, scanners, digital displays, etc.)
423944Circulars/features/emails help me determine what to buyCanadian Tire, Metro/MetroPlus38Offers a good selection of natural productsRCSS, Sobeys, Total LoblawsOffers coupons on items that I want
434046Is conveniently located near meDollarama, Pharmaprix/SDM39Retailer uses technology to improve my shopping experience (app, scanners, digital displays, etc.)WalmartLoyalty/Rewards program rewards me for shopping this retailer
444111Offers good value for the moneyAmazon, Dollarama, No Frills, Walmart40Offers coupons on items that I wantRCSSCirculars/features/emails help me determine what to buy
454220Offers a good selection of products across price pointsAmazon, Walmart41Loyalty/Rewards program rewards me for shopping this retailerMetro/MetroPlus, No Frills, Pharmaprix/SDM, RCSS, Total LoblawsIs conveniently located near me
464321Has good sales/promotionsCanadian Tire, No Frills, Walmart42Circulars/features/emails help me determine what to buyCanadian Tire, IGA/IGA Extra, Metro/MetroPlus, No Frills, Pharmaprix/SDM
474435Membership is worth itAmazon, Costco43Is conveniently located near meDollarama, Pharmaprix/SDMThis retailer is taking steps to protect the environment
484540Provides low prices, even if there's no promotionDollarama, No Frills, WalmartThis retailer has a positive impact on my local community
494643Loyalty/Rewards program rewards me for shopping this retailerCanadian Tire, No Frills, Pharmaprix/SDM, RCSS, Total LoblawsThis retailer provides a safe and hygienic environment for employees and shoppers
504745Offers coupons on items that I want
5148
Sheet1
Click to expand...

Hi Team,

Can someone please look into it :) just help in tweaking few things

Thanks.
Sanjeev
 
Upvote 0
petertenthije said:
You can use this macro.

Please be sure to test this first on a copy of your spreadsheet!

VBA Code: 
Sub Macro1()

' Remove existing data from column J
    Range("J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
  
' Copy contents of column F
    Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

' Paste data into column J
    Range("J2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

' Copy data from column B
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

' Paste data into column J
    Range("J1").Select
    Selection.End(xlDown).Select
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

' Remove duplicates
    ActiveSheet.Range("$J:$J").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub
Click to expand...

Hello Sir,
Hope you are doing good.

Well I have used your macro and I need some help with this

so basically I am pulling my data with Formula and where ever we don't have data I have kept blank
"=IF(INDIRECT("'"&$G$1&$G$2&"'!"&H$3&$A54&"")=0,"",INDIRECT("'"&$G$1&$G$2&"'!"&H$3&$A54&""))"

but in your macro

"Paste data into column J
Range("M5").Select
Selection.End(xlDown).Select
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select"
I getting active cell after few statement as above statement has

"=IF(INDIRECT("'"&$G$1&$G$2&"'!"&H$3&$A54&"")=0,"",INDIRECT("'"&$G$1&$G$2&"'!"&H$3&$A54&""))"

For more details could you please refer to yesterday data file I have sent
Could you please help me with this?

Thanks.
Sanjeev
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Locked
  • Question
Need to New Statement
Replies
1
Views
209
Fluff
Fluff

Forum statistics

Threads
1,215,671
Messages
6,126,133
Members
449,294
Latest member
Jitesh_Sharma

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top