Consolidating Data

%3CLINGO-SUB%20id%3D%22lingo-sub-3247074%22%20slang%3D%22en-US%22%3EConsolidating%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3247074%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20someone%20help%20me%20with%20a%20formula%20or%20macro%20to%20consolidate%20the%20following%20data%20into%20one%20column%20(K)%3F%20I%20need%20it%20to%20say%20the%20frequency%20with%20the%20first%20letter%20of%20the%20week%20day%20to%20follow.%20%22Weekly%20T%22%26nbsp%3B%20I%20am%20doing%20this%20manually%20right%20now%20for%20200%2B%20rows%20and%20I%20need%20an%20easier%20way.%20Any%20help%20is%20appreciated!%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.JPG%22%20style%3D%22width%3A%20860px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F352938i7C1A44A04ABCFD33%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture.JPG%22%20alt%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3247074%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3247507%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3247507%22%20slang%3D%22en-US%22%3EYes!%20So%20there%20is%20never%20a%20mix%20of%20biweekly%2C%20weekly%20or%20monthly%20in%20a%20week.%20Only%20multiple%20weekly%20visits.%20So%20we%20might%20have%20a%20situation%20that%20a%20technician%20is%20visiting%20Monday%2C%20Wednesday%20and%20Friday.%20Never%20a%20random%20Bi-Weekly%20or%20Monthly%20visit%20within%20that%20though.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3247465%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3247465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1323529%22%20target%3D%22_blank%22%3E%40leightob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20i%20understand%20that%20within%20the%20same%20week%20there%20is%20either%20%22weekly%22%20or%20%22monthly%22%20or%20%22bi-weekly%22%20and%20so%20on.%20Therefore%20the%20result%20can%20be%20e.g.%20%22weekly%20M%20W%20F%22%20but%20never%20e.g.%20%22weekly%20W%20bi-weekly%20T%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20solution%20could%20be%20this%20formula%20which%20seems%20to%20work%20in%20my%20spreadsheet%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(COUNTA(A2%3AI2)%3D1%2CINDEX(A2%3AI2%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%2C1)%2CIF(COUNTA(A2%3AI2)%3D2%2CINDEX(A2%3AI2%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C2))%2C1)%2CIF(COUNTA(A2%3AI2)%3D3%2CINDEX(A2%3AI2%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C2))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C3))%2C1)%2CIF(COUNTA(A2%3AI2)%3D4%2CINDEX(A2%3AI2%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C2))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C3))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C4))%2C1)%2CIF(COUNTA(A2%3AI2)%3D5%2CINDEX(A2%3AI2%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CMATCH(1%2CN(NOT(ISBLA%0ANK(A2%3AI2)))%2C0))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C2))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C3))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C4))%2C1)%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C5))%2C1)%2C%22%22)))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3247433%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3247433%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1323529%22%20target%3D%22_blank%22%3E%40leightob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(COUNTA(A2%3AI2)%26gt%3B1%2CINDEX(A2%3AI2%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C1))%26amp%3B%22%20%22%26amp%3BLEFT(LEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C1))%2C1)%2C1)%26amp%3B%22%20%22%26amp%3BINDEX(A2%3AI2%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C2))%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CSMALL(IF(A2%3AI2%26lt%3B%26gt%3B%22%22%2CCOLUMN(%24A%3A%24I))%2C2))%2C1)%2CINDEX(A2%3AI2%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%2C1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20could%20be%20a%20solution%20if%20there%20are%20one%20or%20two%20visits%20a%20week.%20This%20formula%20would%20become%20more%20complicated%20if%20there%20were%20three%20or%20four%20or%20five%20visits%20a%20week.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20i'm%20sure%20that%20there%20is%20an%20easier%20solution%20in%20Excel%20versions%202013%20and%20newer.%20If%20you%20work%20with%20Office365%20or%202021%20a%20possibility%20can%20be%20using%20formulas%20like%20LET%20and%20TEXTJOIN.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3247401%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3247401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3BThis%20looks%20wonderful!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20I%20forgot%20that%20sometimes%20there%20is%20multiple%20visits%20a%20week.%20Is%20there%20a%20way%20to%20add%20this%20too%3F%20Like%20%22Weekly%20M%2C%20W%2C%20F%22%20etc.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MultiVisit.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F353029iF1E52A59F1E0AB6A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22MultiVisit.JPG%22%20alt%3D%22MultiVisit.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3247394%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3247394%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1323529%22%20target%3D%22_blank%22%3E%40leightob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(A2%3AI2%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%26amp%3B%22%20%22%26amp%3BLEFT(INDEX(%24A%241%3A%24I%241%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMaybe%20with%20this%20formula%20if%20you%20want%20to%20return%20e.g.%20%22monthly%20W%20%22.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3247081%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3247081%22%20slang%3D%22en-US%22%3EThat%20definitely%20helps!%20Do%20you%20think%20there%20is%20a%20way%20to%20get%20the%20week%20day%20in%20there%20somehow%3F%3CBR%20%2F%3EI%20am%20very%20grateful%20for%20your%20help%20with%20this!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3247079%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3247079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1323529%22%20target%3D%22_blank%22%3E%40leightob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(A2%3AI2%2CMATCH(1%2CN(NOT(ISBLANK(A2%3AI2)))%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%20It%20works%20in%20my%20spreadsheet%20if%20the%20columns%20B%2C%20D%2C%20F%2C%20H%20and%20J%20are%20empty.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Can someone help me with a formula or macro to consolidate the following data into one column (K)? I need it to say the frequency with the first letter of the week day to follow. "Weekly T"  I am doing this manually right now for 200+ rows and I need an easier way. Any help is appreciated! 

Capture.JPG

 

7 Replies

@leightob 

=INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))

 

Is this what you are looking for? It works in my spreadsheet if the columns B, D, F, H and J are empty. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

That definitely helps! Do you think there is a way to get the week day in there somehow?
I am very grateful for your help with this!

@leightob 

=INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)

 

Maybe with this formula if you want to return e.g. "monthly W ". Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

@Quadruple_Pawn This looks wonderful! I forgot that sometimes there is multiple visits a week. Is there a way to add this too? Like "Weekly M, W, F" etc.MultiVisit.JPG

@leightob 

=IF(COUNTA(A2:I2)>1,INDEX(A2:I2,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),1))&" "&LEFT(LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),1)),1),1)&" "&INDEX(A2:I2,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2))&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2)),1),INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1))

This could be a solution if there are one or two visits a week. This formula would become more complicated if there were three or four or five visits a week.

 

However i'm sure that there is an easier solution in Excel versions 2013 and newer. If you work with Office365 or 2021 a possibility can be using formulas like LET and TEXTJOIN.

@leightob 

Now i understand that within the same week there is either "weekly" or "monthly" or "bi-weekly" and so on. Therefore the result can be e.g. "weekly M W F" but never e.g. "weekly W bi-weekly T".

 

A solution could be this formula which seems to work in my spreadsheet:

=IF(COUNTA(A2:I2)=1,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1),IF(COUNTA(A2:I2)=2,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2)),1),IF(COUNTA(A2:I2)=3,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),3)),1),IF(COUNTA(A2:I2)=4,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),3)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),4)),1),IF(COUNTA(A2:I2)=5,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLA
NK(A2:I2))),0)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),3)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),4)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),5)),1),"")))))

 

Yes! So there is never a mix of biweekly, weekly or monthly in a week. Only multiple weekly visits. So we might have a situation that a technician is visiting Monday, Wednesday and Friday. Never a random Bi-Weekly or Monthly visit within that though.