0

I have a set of data randomly spread as can be seen on the image below. I would like to collect and place them in order of groups (group1, group2,etc..). In the example below, if the value "1" is present in any of the columns A:R, then all the "1"s should be lined in group1. if there are no 1s, then it should appear blank. Is there a way to do this in excel either with a formula or macro? I prefer formula though. Thanks.

Here is the link to the example workbook (editable): https://1drv.ms/x/s!AgVGRfBWRysXgWJTuf-ILJ0BTxyQ

enter image description here

Improve this question
2
  • Can't you just add a Pivot Table of the data and group it?
    – Thales
    Commented Jan 17, 2017 at 13:31
  • I really would prefer a formula or macro. I wouldn't like to switch to a pivot table every time.
    – Max
    Commented Jan 17, 2017 at 13:35

1 Answer 1

Reset to default
1

First of all, the groups must have the same size of Data (just in case the row has just one number). So Group 1 must be from T to AK. On cell T2 enter the formula:

=IFERROR(INDEX($A$2:$R$2,SMALL(IF($A$2:$R$2=1,COLUMN($A$2:$R$2)),COLUMN(A:A))),"")

Type the formula and press Ctrl+Shift+Enter to enter an array formula. Then, when copying and pasting on columns U to AK will show on Group1 all the ocurrences of the 1.

Some tips to improve it:

  • If you put in cell T1 just the number 1 and then custom format it to show "Group " 0, then you can change the 1 on the formula to the address T1. Then, when copying it to the other groups, the formula should adjust to the other numbers (you must put 1 to every column from T to AK, but show just one of them and, form columns AM to BD, there should be the number 2 and so on).
  • If your excel is 2010 or older, then you don't have the IFERROR() function. The workaround is to use an IF() and an ISERROR(): IF(ISERROR(...),"",(...)). The (...) is the formula on the first argument of the IFERROR().
  • To copy the formula to the other rows, you can change the INDEX function to return a row and column. To only difference then is that the returning row is the row of the cell and the column is the formula (...).
Improve this answer
6
  • when I paste the formula in T2 and Ctrl+Shift+Enter, it tells me "you've entered too many arguments for this function". I couldn't get the result. Have you tested this formula already?
    – Max
    Commented Jan 17, 2017 at 15:27
  • What I basically want is to collect the same one digit numbers (say 1, or 2 ), and then line them up together. 1s are separate. 2s are separate. It goes on like this. but their number is not fixed for every row. There may be no 1s in the row, or just two of them or three. this makes it tricky.
    – Max
    Commented Jan 17, 2017 at 15:38
  • Sorry, there was a typo on the formula. I already corrected it. The first COLUMN() function was wrong. Instead, is the SMALL() function. Tested here and it works.
    – Thales
    Commented Jan 17, 2017 at 16:38
  • =INDEX($A$2:$R$2,SMALL(IF($A$2:$R$2=1,COLUMN($A$2:$R$2)),COLUMN(A:A))) should be the right thing
    – Thales
    Commented Jan 17, 2017 at 16:39
  • I tried to do this with index function but couldn't figure it out. yes, it works beautifully. Thanks a lot!
    – Max
    Commented Jan 17, 2017 at 16:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .