Convert One Column Address List Same & Different Row Amounts
When you get a list of addresses, sometimes it’s in a good format to do a mail merge. That’s when you’ve got the name is one column, address in another column, etcetera. If you get an address list with all that information in ONE column, it’s not that hard to copy and paste transpose to another set of cell ranges IF there aren’t too many records (less than five).
When you get a list of records that are higher, say more than 10 records, then it becomes more time consuming. Get to 20+ records, and you might as well forget about taking the afternoon off. BUT if the records follow a consistent and static structure, then you can use some formula tricks to take care of it quite easily. AND if the records don’t follow a structure, but have some sort of pattern, there’s also a way to take care of that and unstack this data.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to
www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
Пікірлер: 19
I have to mail out hundreds of 1099 forms for tax season to all my customers and my accountant sent me the list of my recipients in a PDF file listed vertically since her Tax Software can't export the list in excel. Thus, i had to learn this and i must say.... I am VERY HAPPY. THANK YOU!
Nice one Doug! Here are a couple of other formulas that would work: =INDEX($A$1:$A$16,COLUMNS($E1:E1)+(ROWS(E$1:E1)-1)*4) and =WRAPROWS(A1:A16,4). Thumbs up!!
@DougHExcel
Жыл бұрын
Wayne! Wow...that's awesome! I'll need to try that 😀
Thank you sir this helped me save a lot of time ❤
@DougHExcel
Ай бұрын
Glad it helped
Really liked the first trick 👌We can also use new Excel Function - WRAPROWS for this purpose. =WRAPROWS(DataRng, 5). If blanks a there, =WRAPROWS(TOCOL(DataRng,3),5)
@DougHExcel
Жыл бұрын
Thanks! Oh my...I'll need to look into WRAPROWS now 😄
Excellently narrated. Converted my address list from text into tables. Thanks a lot.
@DougHExcel
Жыл бұрын
Glad it was helpful!
Thank you for your informative videos. I have a particular case in excel in which I couldn’t find any solution to it and I hope you can help me with it. If I have various numbers in column B, and I need these numbers to be (increased/decreased): • By 0.5 either to make it 20.5 or 19.5 • Or by 1 to make it either 21 or 19 • Or 2 to make it 22 or 18 …etc. • The change should be based on two conditions, a selected text from a drop-down list in the next cell in column C in addition to a number I type in the cell next to them in column D, for example: • The first case scenario is: When I type the number 20 in cell B2 and then I choose the text “increase” from a drop-down list in cell C2 and then I type 0.5 in the cell D2, I want this “increase” to be reflected on B2 and make it 20.5 • The second case scenario: When I type the number 15 in cell B3 and then I choose the text “decrease” from a drop-down list in cell C3 and then I type any number like 1, 2 or 3 in the cell D3, I want this “decrease” to be reflected on B3 and make it 14, 13 0r 12 • The third case scenario: When I type number 10 in cell B4 and then I don’t choose any text from a drop-down list in cell C4 and leave it blank with cell D4, I don’t want any change to be reflected on B4 and leave it 10 as it is. I hope you can find time to answer this very soon. Thanks again.
Thank you for this, I deal with mailing lists that consist of thousands of records.
@DougHExcel
5 ай бұрын
You're very welcome!
Very Cool!
@DougHExcel
Жыл бұрын
Hi Djl, thanks for the comment!
Thank u so much sir ❤❤ really
@DougHExcel
11 ай бұрын
Hi Sabira Bano Shaikh, you're welcome!
I have over 7000+ cells of addresses coming out to about 1500 ish people, some have 4 lines of address and their name (so 5 cells), and some only have 3 cells and their name (so 4 cells), however there are no gaps between each person so there is no "pattern" any chance I'm not stuffed here? xD
Complicated Doug, I watch all ur videos but this one is not g8
@DougHExcel
Жыл бұрын
Rajesh Banavar, that is very interesting...thanks for letting me know.