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
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 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Block | Product no | Description | Option A | Option B | Duplicate | ||
2 | 1 | 101 | BMW 3series | Sunroof | Radio | 3 | ||
3 | 101 | BMW 3series | Sunroof | Radio | 3 | |||
4 | 101 | BMW 3series | leather | Xeon | 1 | |||
5 | 101 | BMW 3series | leather | Radio | 1 | |||
6 | 1 | 333 | Mercedes C class | Sunroof | Radio | 1 | ||
7 | 333 | Mercedes C class | Sunroof | HUD | 1 | |||
8 | 1 | 444 | BMW 5 series | AirCon | Radio | 2 | ||
9 | 444 | BMW 5 series | AirCon | USB | 1 | |||
10 | 444 | BMW 5 series | AirCon | Radio | 2 | |||
11 | 2 | 101 | BMW 3series | Sunroof | Radio | 3 | ||
12 | 101 | BMWW 3series missspelled | Sunroof | Radio | 1 | |||
13 | 101 | BMW 3series | Tyres | Autopilot | 1 | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F13 | F2 | =COUNTIFS($B:$B,$B2,$C:$C,$C2,$D:$D,$D2,$E:$E,$E2) |