Hello. Does anybody know how to do this. I am looking for a formula for column D.
All I could come up with so far is this:
D2=B2&REPT(" ",MAX(LEN(B2),LEN(B3))−LEN(B2))&"|"&C2&REPT(" ",MAX(LEN(C2),LEN(C3))−LEN(C2))&"|"
Not so good :-|
MacBook Pro (17-inch Early 2009), OS X Yosemite (10.10.5), null
Hi joshua,
Are you looking for something like this?
quinn
edit: On re examination I guess not.
Why do you need this? It looks like there can be any number of similar IDs. Is there a bigger picture that might suggest another approach?
q
No, but thanks. That would result in the following table. The vertical lines in column D should be aligned with the other cells with matching ID #.
Hi joshua,
Are your IDs all three digits?
quinn
Yes, in the above example. In my actual table, they are 5 digits.
Hi joshua,
So they are all 5 digits. We can work with that.
quinn
For instance, for ID 101, "Appleseed" (B4) is the name with the most characters (9). So now all the other names with ID = 101, which are shorter, need to have additional space characters added. So "Brown" (B5) needs to be "Brown ".
Hi joshua,
I got that. What is the bigger picture here? How are you using this result?
quinn
are you trying to align the names? If so is there a reason not to use the column in which the name exist to align the names?
I bigger picture of the problem would help us help you
dup
I need to convert a table into pure text, monospace font, with different column width for each ID.
Hello
You may try the following table. It is assumed rows are sorted by column A. D:I are auxiliary columns which you can hide if you want to.
Table 1 (excerpt) A1 id A2 100 A3 100 A4 101 A5 101 B1 surname B2 Jones B3 Smith B4 Appleseed B5 Brown C1 name C2 Jerry C3 Max C4 John C5 Charlie D1 i D2 =MATCH(A2,A,0) D3 =MATCH(A3,A,0) D4 =MATCH(A4,A,0) D5 =MATCH(A5,A,0) E1 j E2 =MATCH(A2,A,1) E3 =MATCH(A3,A,1) E4 =MATCH(A4,A,1) E5 =MATCH(A5,A,1) F1 p F2 =LEN(B2) F3 =LEN(B3) F4 =LEN(B4) F5 =LEN(B5) G1 q G2 =LEN(C2) G3 =LEN(C3) G4 =LEN(C4) G5 =LEN(C5) H1 px H2 =MAX(OFFSET(F$1,$D2-1,0,$E2-$D2+1,1)) H3 =MAX(OFFSET(F$1,$D3-1,0,$E3-$D3+1,1)) H4 =MAX(OFFSET(F$1,$D4-1,0,$E4-$D4+1,1)) H5 =MAX(OFFSET(F$1,$D5-1,0,$E5-$D5+1,1)) I1 qx I2 =MAX(OFFSET(G$1,$D2-1,0,$E2-$D2+1,1)) I3 =MAX(OFFSET(G$1,$D3-1,0,$E3-$D3+1,1)) I4 =MAX(OFFSET(G$1,$D4-1,0,$E4-$D4+1,1)) I5 =MAX(OFFSET(G$1,$D5-1,0,$E5-$D5+1,1)) J1 J2 =B2&REPT(" ",H2-F2)&" | "&C2&REPT(" ",I2-G2)&" | " J3 =B3&REPT(" ",H3-F3)&" | "&C3&REPT(" ",I3-G3)&" | " J4 =B4&REPT(" ",H4-F4)&" | "&C4&REPT(" ",I4-G4)&" | " J5 =B5&REPT(" ",H5-F5)&" | "&C5&REPT(" ",I5-G5)&" | "
Notes.
Formulae in D2 and E2 can be filled down.
Formula in F2 can be filled down and right across F:G.
Formula in H2 can be filled down and right across H:I.
Formula in J2 can be filled down.
D and E calculate the starting and ending row indices for the id in A.
F and G calculate the length of text in B and C.
H and I calculate the maximum values in range in F and G for the id in A.
Table is built with Numbers v2.
Hope this may help,
H
Hi Joshua,
This was a fun puzzle. It looks like Hiroto has it covered but here is my variation.
D2=COUNTIF(A,A2)
E2=MATCH($A2,$A,0)
F2=E2+D2−1
G2="H"&E2&":H"&F2
H2=LEN(B2)
I2=MAX(INDIRECT(G2))
J2=B2&REPT(" ",I2−H2)&"| "&C2&" |"
You could of course roll these together into more massive formulas but it is easier to simply hide them.
It occurs to me if you use Hiroto's method to find the last row of an ID you could eliminate my Column D.
quinn
Thanks. This looks like a solid solution. This is exactly what I was looking for. 🙂
My pleasure ! 🙂
MAX & LEN of an array
