Need a macro to conduct some steps for each part number in a column

[MacroTestQ]

I have an Excel sheet with 12 columns and 4000+ rows. In Column E, I have a list of part numbers. These part numbers may or may not be repeated based on the city names found in Column F. For example, Part A (located in Column E) may be in 3 different rows: one row for City X, one row for City Y, and one row for City Z.

I would like to have a macro that goes through each part number and checks if there are 3 instances of that part number showing up: one associated with City X, one associated with City Y, and one associated with City Z.


If there are 3 instances, I would like the macro to make the cost the same for all the instances. The cost value is found in Column G.

If there are not 3 instances, I would like the macro to create a new row, or rows, and add the missing city, or cities in Column F to that row or rows.

Is this possible?


All columns start their data in Row 2 (Row 1 contains the headers).

Please view the attached images for a simplified example of what I currently have and what I'd like the end result to be.

Please let me know if you have any questions - I'd be happy to clarify.

 

[alansidman]

Cannot manipulate data in a picture. Please reload with XL2BB so that we can help you without having to retype all your data.

 

[MacroTestQ]

Hey Alan, thanks for your response. Please find my simplified example below:

Current:

Sample Sheet.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1	Column w/ Text 1	Column w/ Text 2	Column w/ Text 3	Item	Location	Cost	Column w/ Text 4	Column w/ Text 5	Column w/ Text 6	Column w/ Text 7	Column w/ Text 8	Column w/ Text 9
2	-	-	-	X1	City A	$10	-	-	-	-	-	-
3	-	-	-	X1	City B	$9	-	-	-	-	-	-
4	-	-	-	X2	City A	$15	-	-	-	-	-	-
5	-	-	-	X2	City B	$15	-	-	-	-	-	-
6	-	-	-	X2	City C	$12	-	-	-	-	-	-
7	-	-	-	X3	City A	$20	-	-	-	-	-	-
8	-	-	-	X4	City A	$25	-	-	-	-	-	-
9	-	-	-	X4	City C	$20	-	-	-	-	-	-
10	-	-	-	X5	City A	$30	-	-	-	-	-	-
Current

What I want the end result to be:

Sample Sheet.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1	Column w/ Text 1	Column w/ Text 2	Column w/ Text 3	Item	Location	Cost	Column w/ Text 4	Column w/ Text 5	Column w/ Text 6	Column w/ Text 7	Column w/ Text 8	Column w/ Text 9
2	-	-	-	X1	City A	$10	-	-	-	-	-	-
3	-	-	-	X1	City B	$10	-	-	-	-	-	-
4	-	-	-	X1	City C	$10	-	-	-	-	-	-
5	-	-	-	X2	City A	$15	-	-	-	-	-	-
6	-	-	-	X2	City B	$15	-	-	-	-	-	-
7	-	-	-	X2	City C	$15	-	-	-	-	-	-
8	-	-	-	X3	City A	$20	-	-	-	-	-	-
9	-	-	-	X3	City B	$20	-	-	-	-	-	-
10	-	-	-	X3	City C	$20	-	-	-	-	-	-
11	-	-	-	X4	City A	$25	-	-	-	-	-	-
12	-	-	-	X4	City C	$25	-	-	-	-	-	-
13	-	-	-	X4	City B	$25	-	-	-	-	-	-
14	-	-	-	X5	City A	$30	-	-	-	-	-	-
15	-	-	-	X5	City B	$30	-	-	-	-	-	-
16	-	-	-	X5	City C	$30	-	-	-	-	-	-
Needed

 

[MacroTestQ]

Cannot manipulate data in a picture. Please reload with XL2BB so that we can help you without having to retype all your data.

Please see above for the XL2BB content, thanks!

 

[alansidman]

In the new release of Excel 365 for MAC Insider, you have Power Query available to use. Here is the Mcode for Power Query to achieve your expected results.

Look here-->Import and shape data in Excel for Mac (Power Query)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Item"}, {{"MaxCost", each List.Max([Cost]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "City A", each "City A"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "City B", each "City B"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "City C", each "City C"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom2", {"Item", "MaxCost"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Item", "Attribute", "MaxCost"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute", "City"}})
in
    #"Renamed Columns"

 

[MacroTestQ]

In the new release of Excel 365 for MAC Insider, you have Power Query available to use. Here is the Mcode for Power Query to achieve your expected results.

Look here-->Import and shape data in Excel for Mac (Power Query)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Item"}, {{"MaxCost", each List.Max([Cost]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "City A", each "City A"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "City B", each "City B"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "City C", each "City C"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom2", {"Item", "MaxCost"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Item", "Attribute", "MaxCost"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute", "City"}})
in
    #"Renamed Columns"

Hey Alan, thanks for your help! Unfortunately, I don't have access to the Power Query Editor on my current Excel version. Although it is 16.62, the "Launch Power Query Editor" option doesn't show up under Get Data (Power Query). What can I do about this?

 

[alansidman]

Unfortunately, unless you sign up for the insider as indicated in the link, then PQ is not available to you. If you don't do that, then patience that someone will supply a VBA solution.

 

[Jim Gordon]

Hey Alan, thanks for your help! Unfortunately, I don't have access to the Power Query Editor on my current Excel version. Although it is 16.62, the "Launch Power Query Editor" option doesn't show up under Get Data (Power Query). What can I do about this?

Please check your version and license type. You must have a subscription license type to have PowerQuery. Use the Menu Bar. Choose Excel > About Excel to display the version and license type. If your subscription is managed by a an education institution or IT department, they can turn PowerQuery off.