How to group / arrange / match groups of cells above corresponding cells?

**roseuz** · 06-28-2017, 08:22 PM

This seems like it should be simple, but I have tried and tried multiple formulas and can't come up with a solution.

This is how my data is arranged, starting from Row 2. Row 1 has headers.

First word is in Column A, second set of data to the right is in column B.

Box XY
Box ZZ
Box AA
Box AB
Cup AD
Bowl ZA
Plate ZL
Plate QR
Plate KQ

I want the data to look like this in another column:

Box
XY
ZZ
AA
AB
Cup
AD
Bowl
ZA
Plate
ZL
QR
KQ

**FlameRetired** · 06-28-2017, 09:02 PM

roseuz I am working on an idea (I hope). I noticed your join date is 2012 and profile says Excel 2007. Is there any change you have since upgraded?

It might help.

**roseuz** · 06-28-2017, 09:25 PM

Thanks.

Nope, still 2007. 2016 at work, though I would prefer a solution that also works in 2007.

**FlameRetired** · 06-28-2017, 09:26 PM

10-4. Thanks.

**FlameRetired** · 06-28-2017, 10:36 PM

Try array entering this formula in C. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

By the way. I borrowed heavily from XOR LX. The site specializes in these kind of challenges. If you're interested look up articles on re dimensioning.

https://excelxor.com/category/redimensioning/

**FlameRetired** · 06-28-2017, 11:47 PM

If you are open to a helper column this is much simpler.

For the helper in C2 and filled down until you get blanks.

Formula:

Please Login or Register  to view this content.

Then array enter this in D2 and fill down until you get blanks.

Formula:

Please Login or Register  to view this content.

Row\Col	A	B	C	D
1	Data		Helper	Formula
2	Box	XY	Box	Box
3	Box	ZZ	XY	XY
4	Box	AA	Box	ZZ
5	Box	AB	ZZ	AA
6	Cup	AD	Box	AB
7	Bowl	ZA	AA	Cup
8	Plate	ZL	Box	AD
9	Plate	QR	AB	Bowl
10	Plate	KQ	Cup	ZA
11			AD	Plate
12			Bowl	ZL
13			ZA	QR
14			Plate	KQ
15			ZL
16			Plate
17			QR
18			Plate
19			KQ

How to group / arrange / match groups of cells above corresponding cells?

LinkBack

Thread Tools

Rate This Thread

Display

How to group / arrange / match groups of cells above corresponding cells?

Re: How to group / arrange / match groups of cells above corresponding cells?

Re: How to group / arrange / match groups of cells above corresponding cells?

Re: How to group / arrange / match groups of cells above corresponding cells?

Re: How to group / arrange / match groups of cells above corresponding cells?

Re: How to group / arrange / match groups of cells above corresponding cells?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] (VBA) How Assign Column Cell To Corresponding Group, and Paste Group Cells into Groups WS?

[SOLVED] Unmerge groups of cells within single column and repopulate newly unmerged, empty cells

[SOLVED] Move the content of specific groups of cells in column C to the first cell in that group

[SOLVED] Formula to search for a value in groups of cells and return the name of the smallest group

Macro that will swap data if I drag a group of cells on top of another group of cells

Analysing groups of cells to form new groups

Using values from one group of cells to format a separate group of cells

Bookmarks

Bookmarks

Posting Permissions