Need to find distributed rows that are spread but should be ongoing

Excel4Dummy

New Member
Joined
May 12, 2019
Messages
3
Hey guys,
I have a really stupid question, but I am stuck and need some help.
Two things need to be achived
1. Eliminate all duplicates within a block (Product no)
2. Find matching entries of the same product numbers that are "lost" somewhere in the sheet and consolidate it to one block (wherever in the sheet).

The duplicate counter already helps to find identify duplicates (columns B-E identical).
The problem is that the product description might be misspelled or slightly written differently and needs to be edited manually to meet naming conventions.
In order to consolidate Product number 101 I need to find row 11, 12 and 13.

My idea was to read out the row number of a Product no (e.g. 101) and test if the cell addresses are ongoing. Here B2-B5 and B11-B13.
It would be perfect if I had e.g. at the end of the first block in cell G5 the address of B11 as indication that the Product no continues here. But I could easily live with TRUE or FALSE in column G.
When I filter for 101 on column B I see it anyway visually but the problem is the large number of Product numbers. It probably would take a week or more to do that manually.

Any help appreciated!
Have a nice weekend,
Excel4Dummy

p.s. Column G is empty


Excel Problem Demo.xlsx
ABCDEF
1BlockProduct noDescriptionOption AOption BDuplicate
21101BMW 3seriesSunroofRadio3
3101BMW 3seriesSunroofRadio3
4101BMW 3seriesleatherXeon1
5101BMW 3seriesleatherRadio1
61333Mercedes C classSunroofRadio1
7333Mercedes C classSunroofHUD1
81444BMW 5 seriesAirConRadio2
9444BMW 5 seriesAirConUSB1
10444BMW 5 seriesAirConRadio2
112101BMW 3seriesSunroofRadio3
12101BMWW 3series missspelledSunroofRadio1
13101BMW 3seriesTyresAutopilot1
Sheet1
Cell Formulas
RangeFormula
F2:F13F2=COUNTIFS($B:$B,$B2,$C:$C,$C2,$D:$D,$D2,$E:$E,$E2)
 

Attachments

  • Excel demo problem.jpg
    Excel demo problem.jpg
    115.7 KB · Views: 4

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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