Member Avatar for Amaina

I have checked everywhere in Daniweb to see if this has been asked before but could not find the question. Forgive me if this is too simple an issue and it is my hope that my question is clear.

I have an excel worksheet (see attached) with 4 columns. Column A is the ID, Column B is title A, column C is title B and column C is count.

Column A and B are related in that each title A has a unique ID. Column C and D are related and each Title B has a unique count. Column A and C are made up of duplicate text only that they are not synced in order. My aim is to have a unique list of of Titles and their corresponding counts.I welcome your ideas on how best to solve this.

data.xlsx (7.89 KB)
  • 1 Contributor
  • 1 Reply
  • 443 Views
  • 1 Minute Discussion Span
  • Latest Post Latest Post by Amaina

Dani AI

Generated

The OP () has two related lists: an ID↔Title pairing (cols A–B) and a Title↔Count pairing (cols C–D). The reliable approach is to normalize the title text, then either (a) bring counts next to the ID/title rows with a lookup, or (b) produce a single aggregated list of titles + counts (and then join IDs). Below are practical, low-friction options and a MySQL pattern for the same result.

Excel — quick formulas

  • If the counts in C/D may repeat and should be summed per title, use SUMIF:

    =SUMIF($C:$C, B2, $D:$D)

  • If there should be exactly one match and a direct lookup is preferred, use INDEX/MATCH with cleaning and error handling:

    =IFERROR(INDEX($D:$D, MATCH(TRIM(SUBSTITUTE(B2, CHAR(160), " ")), $C:$C, 0)), "")

  • Create a unique list with Data → Remove Duplicates (or UNIQUE() in Excel 365) and then apply the SUMIF on that list.

Excel — Power Query / Pivot

  • For larger or messy datasets, use Power Query: load both ranges, optionally append them, Group By title to sum counts, then Merge (left join) to bring IDs from the A/B table. Power Query also offers a Fuzzy Merge option for near-matches.

MySQL pattern

  • Aggregate counts from the title/count table, normalize text, then join to the id/title table:

    -- aggregate counts by normalized title
SELECT LOWER(TRIM(REPLACE(titleB, CHAR(160), ' '))) AS title_norm,
       SUM(cnt) AS total_cnt
FROM t2
GROUP BY title_norm;
    -- join aggregated counts to id/title list
SELECT t1.id, t1.title,
       COALESCE(s.total_cnt, 0) AS total_cnt
FROM t1
LEFT JOIN (
  SELECT LOWER(TRIM(REPLACE(titleB, CHAR(160), ' '))) AS title_norm,
         SUM(cnt) AS total_cnt
  FROM t2
  GROUP BY title_norm
) s ON LOWER(TRIM(REPLACE(t1.title, CHAR(160), ' '))) = s.title_norm;

Troubleshooting notes

  • Watch hidden chars, non‑breaking spaces, inconsistent casing, trailing punctuation. Use LEN() comparisons or helper columns with TRIM()/CLEAN() to spot differences. For typos, consider fuzzy matching (Power Query fuzzy merge or SOUNDEX/Levenshtein approaches in SQL) and manual review.
Member Avatar for Amaina

Suggestions on how this can be solved on MySQL are also welcomed

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.